MySQL:如何查询出每个 Group 的 Top n 条记录?
source link: http://mp.weixin.qq.com/s?__biz=MzA4Nzc4MjI4MQ%3D%3D&%3Bmid=2652403371&%3Bidx=1&%3Bsn=28c03df97b58d60913637aa6c9c6fd69
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
问题描述
需求:
查询出每月 order_amount(订单金额)
排行 前3 的记录。
例如对于 2019-02
,查询结果中就应该是这3条:
解决方法
MySQL 5.7 和 MySQL 8.0 有不同的处理方法。
1. MySQL 5.7
我们先写一个查询语句。
根据 order_date
中的 年、月
,和 order_amount
进行 降序 排列。
然后,添加一个新列: order_amount
(本条记录在本月中的名次)。
执行结果:
可以看到,根据 年、月、订单金额
排序了,还多了一列 order_rank
,显示出了本条记录在本月的订单金额排名情况。
上面SQL中比较个性的是这部分:
@current_month
和 @order_rank
是我们自定义的变量。
使用 :=
可以动态创建一个变量,而不需要使用 set
命令。
这句的含义:
取得 order_date
中的月份值,赋值给 current_month
,这样就可以跟踪每个月份。
这句的含义:
比较 current_month
和本条记录中的月份,如果一样, order_rank
自增 1 ,否则,置为 1 。
注意, @current_month
是在 @order_rank
的后面,例如执行到这条记录时:
if
判断中, MONTH(order_date)
值为 2 ,而 current_month
值为 1 ,还是上条记录设置的。
接下来,把上面的SQL语句作为一个子查询,然后使用一个 where
条件就可以轻松拿到每组的 top 3。
最终语句:
执行结果:
2. MySQL 8
MySQL 8 引入了一个 rank()
函数,可以更简便的实现排行的功能。
执行结果:
效果和 5.7 中的方法是一致的。
我们看下语句中的 rank()
方法:
-
PARTITION BY
是指定分区依据,这里是根据订单的年、月
进行分区。 -
ORDER BY
指定了分区内的排序依据,这里是根据订单的年、月、金额
进行 降序 排列。
这样就会自动计算出排行数值。
需要注意的是,这个地方和 5.7 的方法不一样:
就是参与排序的几个值一样的时候, rank
值是一样的。
最终的SQL语句:
翻译整理自:
https://towardsdatascience.com/mysql-how-to-write-a-query-that-returns-the-top-records-in-a-group-12865695f436
如果您有兴趣实践一下,公众号中发送消息: 200106 ,会回复实践笔记的下载地址,包含建表语句、测试数据、MySQL5.7和8.0的这2个查询语句。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK