在MySQL中如何选择每个组中的前2行？

在实际项目中，我们常常需要对数据进行分组并选择每个组的前几行数据。比如，我们需要找出每个分类的最新2篇文章、每个部门的最高销售额的前2名等。

MySQL提供了一些便捷的函数和操作符，可以方便地实现选择每个组中的前2行。下面将结合示例代码详细讲解。

阅读更多：MySQL 教程

示例数据

我们以文章分类表（article）为例，假设表中数据如下：

id	title	category_id	created_at
1	Redis入门指南	1	2021-01-01 10:00:00
2	MySQL优化方案总结	2	2021-01-02 12:00:00
3	PHP7新特性介绍	2	2021-01-03 14:00:00
4	Linux命令大全	3	2021-01-04 16:00:00
5	Docker容器使用指南	3	2021-01-05 18:00:00
6	MySQL优化方案进阶版	2	2021-01-06 20:00:00

使用子查询和LIMIT

最常见的方法是使用子查询和LIMIT，即先筛选出每个组的全部数据，再使用LIMIT选取前2行。

SELECT *
FROM article
WHERE id IN (
  SELECT id
  FROM (
    SELECT id
    FROM article
    ORDER BY category_id, created_at DESC -- 按分类和创建时间倒序排序
  ) AS tmp
  GROUP BY category_id
  HAVING COUNT(*) <= 2 -- 选择每个组的前2行数据
);

解析：

内层子查询SELECT id FROM article ORDER BY category_id, created_at DESC将所有数据按分类和创建时间的倒序排序，并只保留文章ID（id）这一列。
外层子查询将按分类和创建时间倒序排序的文章ID按分类分组，并且只选取每个组的前2篇文章。
最终查询结果中只显示选取的文章记录。

使用变量

另一种方式是使用变量保存当前分类的行数，然后对每个分类记录进行判断。具体操作如下：

SELECT *
FROM (
  SELECT *, @order := IF(@tmp_category_id = category_id, @order + 1, 1) AS row_number, @tmp_category_id := category_id
  FROM article
  ORDER BY category_id, created_at DESC -- 按分类和创建时间倒序排序
) AS tmp
WHERE row_number <= 2;

解析：

内层查询SELECT *, @order := IF(@tmp_category_id = category_id, @order + 1, 1) AS row_number, @tmp_category_id := category_id FROM article ORDER BY category_id, created_at DESC将所有数据按分类和创建时间的倒序排序，并增加一个列row_number来保存当前分类的已选取行数。
@order := IF(@tmp_category_id = category_id, @order + 1, 1)表示当前分类与上一行分类相同，则行数加1，否则行数为1。
@tmp_category_id := category_id表示当前行的分类ID保存到变量中。
外层查询只保留row_number从1到2的记录。

使用窗口函数

如果MySQL版本高于8.0，则可以使用窗口函数来选取每个组的前几行，代码如下：

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY created_at DESC) AS row_number
  FROM article
) AS tmp
WHERE row_number <= 2;

解析：

内层查询SELECT *, ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY created_at DESC) AS row_number使用窗口函数ROW_NUMBER()来为每个组内的记录编号，其中PARTITION BY定义分组列，ORDER BY定义排序列，因此窗口函数会在每个组内按时间倒序分配行号。
外层查询只保留row_number从1到2的记录。