1112. 每位学生的最高成绩 🔒

题目描述

表：Enrollments

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| course_id     | int     |
| grade         | int     |
+---------------+---------+
(student_id, course_id) 是该表的主键（具有唯一值的列的组合）。
grade 不会为 NULL。

编写解决方案，找出每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。

查询结果格式如下所示。

示例 1：

输入：
Enrollments 表：
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 2          | 2         | 95    |
| 2          | 3         | 95    |
| 1          | 1         | 90    |
| 1          | 2         | 99    |
| 3          | 1         | 80    |
| 3          | 2         | 75    |
| 3          | 3         | 82    |
+------------+-----------+-------+
输出：
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 1          | 2         | 99    |
| 2          | 2         | 95    |
| 3          | 3         | 82    |
+------------+-----------+-------+

解法

方法一：RANK() OVER() 窗口函数

思考

每名学生取最高分，并列时取最小 course_id。RANK() OVER (PARTITION BY student_id ORDER BY grade DESC, course_id) 把这一字典序一次排好，名次为 \(1\) 的行即为所求，再按 student_id 输出。

我们可以使用 RANK() OVER() 窗口函数，按照每个学生的成绩降序排列，如果成绩相同，按照课程号升序排列，然后取每个学生排名为 \(1\) 的记录。

MySQL

# Write your MySQL query statement below
WITH
    T AS (
        SELECT
            *,
            RANK() OVER (
                PARTITION BY student_id
                ORDER BY grade DESC, course_id
            ) AS rk
        FROM Enrollments
    )
SELECT student_id, course_id, grade
FROM T
WHERE rk = 1
ORDER BY student_id;

方法二：子查询

思考

方法一依赖窗口函数。若环境不便使用，可先按学生聚合 MAX(grade)，再在原表中筛出该成绩并对 course_id 取 MIN。两次聚合分别落实「最高分」与「同分课程号最小」两个条件。

我们可以先查询每个学生的最高成绩，然后再查询每个学生的最高成绩对应的最小课程号。