MySQL：如何查询出每个 Group 的 Top n 条记录？

问题描述

需求：

查询出每月 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个查询语句。