MySQL查询每组中最大值的前N条记录

在数据查询中，经常会遇到按特定字段分组并在每组中选取前 N 条记录的需求。假设我们有一个学生成绩表 score ，包含字段 subject（学科）、 name（学生名字）和score（分数），我们的任务是查询每个学科的前三名。

本文将讲解如何在 MySQL 8.0 及以上版本中使用窗口函数实现这一需求，并探讨如何在 MySQL 8.0 以下版本中达成同样的效果。

---

场景描述

假设 score 表的数据结构如下：

subject	name	score
语文	张三	89
语文	李四	90
语文	王五	77
语文	赵六	85
语文	钱七	69
数学	张三	59
数学	李四	98
数学	王五	77
数学	赵六	65
数学	钱七	39

在此数据结构下，每条记录表示某个学生某一科目的成绩。我们希望查询每个学科中 score 最大的前三条记录。

---

MySQL 8.0 及以上版本：使用窗口函数 ROW_NUMBER()

在 MySQL 8.0 及以上版本，可以使用窗口函数 ROW_NUMBER() 为每组数据分配行号，从而筛选出每组中的前 N 条记录。

查询语句

以下 SQL 语句可以获取每个 subject 中 score 最大的前三条记录：

SELECT 
    *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY `subject` ORDER BY score DESC) AS row_num
    FROM 
        score
) AS ranked
WHERE 
    row_num <= 3;

查询逻辑解析

1. ROW_NUMBER()：为每条记录分配行号。

   • PARTITION BY subject 按 subject 将数据划分成多个分组。
   • ORDER BY score DESC 使得每组中的 score 值从大到小排列。

2. 外层查询过滤结果：

   • WHERE row_num <= 3 保留每组中行号小于等于 3 的记录，得到每个学科 score 最大的前三条记录。

查询结果示例

在上面的示例数据下，查询结果如下：

subect	name	score	row_num
数学	李四	98	1
数学	王五	77	2
数学	赵六	65	3
语文	李四	90	1
语文	张三	89	2
语文	赵六	85	3

---

MySQL 8.0 以下版本：使用子查询和 JOIN 实现

在 MySQL 5.7 及以下版本中，窗口函数不可用。我们可以通过定义变量的方式来模拟同样的效果。我们使用两个变量：@sub和@rank来表示当前的学科和排行：

查询语句

假设我们想查询每个 subject 中 score 最大的前三条记录，可以按以下步骤完成：

SELECT `subject`, `name`, `score`
FROM (
    SELECT
        score.*,
         @rank := IF(@sub = `subject`, @rank + 1, 1) AS `rank`,
         @sub := `subject`
    FROM score
    ORDER BY `subject`, score DESC
) AS ranked
WHERE `rank` <= 3;

查询逻辑解析

1. 使用 @sub 记录当前组的名称。
2. 使用 @rank 对每个组的记录进行排名：
   • 如果当前记录与上一次记录属于同一组，则排名递增。
   • 如果是新组，则排名从 1 开始。
3. 在外部查询中过滤出排名在 3以内的记录。

查询结果

这将返回与使用窗口函数的查询相同的结果，但性能在数据量较大时可能不如窗口函数方法。

---

总结

本文介绍了如何在 MySQL 中按分组查询每组中最大值的前 N 条记录。在 MySQL 8.0 及以上版本，可以借助 ROW_NUMBER() 窗口函数实现高效查询；在 MySQL 8.0 以下版本中，使用子查询和记录变量也能达到相同效果。

---

参考资料

• MySQL 8.0 窗口函数 - 官方文档
• MySQL 5.7 子查询 - 官方文档
• MySQL 排序和分组查询 - 官方文档