如何用MySQL查询每个学生的最高分记录？

高效获取每个学生的最高分记录：MySQL查询语句详解

本文将演示如何使用MySQL查询语句，从score表中提取每个学生的最高分记录。score表结构如下：

id	student_id	score	date
1	1	80	20251118
2	2	88	20251118
3	1	78	20251118
4	2	98	20251118
5	3	89	20251117
6	2	72	20251117
7	1	63	20251117
8	3	81	20251117
9	1	67	20251116
10	2	86	20251116

目标：获取每个学生student_id最近一次考试的最高分记录。

解决方案：结合子查询和窗口函数ROW_NUMBER()，我们可以高效地实现这一目标：

SELECT id, student_id, score, date
FROM (
    SELECT *, 
        ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY score DESC, date DESC) AS ranking
    FROM score
) AS tmp
WHERE ranking = 1;

语句解析：

1. 内部查询: SELECT *, ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY score DESC, date DESC) AS ranking FROM score

   • ROW_NUMBER()是一个窗口函数，它为每个分区分配一个唯一的顺序号。
   • PARTITION BY student_id 将数据按student_id分组，为每个学生创建一个分区。
   • ORDER BY score DESC, date DESC 在每个分区内，按score降序排列，如果分数相同，则按date降序排列，确保最近一次考试的最高分排在最前。
   • AS ranking 将生成的排名命名为ranking。

2. 外部查询: SELECT id, student_id, score, date FROM (...) AS tmp WHERE ranking = 1;

   • 外部查询从内部查询的结果tmp中选择id, student_id, score, date列。
   • WHERE ranking = 1 筛选出每个分区中排名为1的行，即每个学生的最高分记录。

此方法清晰高效地解决了问题，避免了复杂的连接查询，是处理此类问题的最佳实践。