Question

1112. 每位学生的最高成绩

English Version

题目描述

表：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() 窗口函数

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

# 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;

方法二：子查询

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

# Write your MySQL query statement below
SELECT student_id, MIN(course_id) AS course_id, grade
FROM Enrollments
WHERE
  (student_id, grade) IN (
    SELECT student_id, MAX(grade) AS grade
    FROM Enrollments
    GROUP BY 1
  )
GROUP BY 1
ORDER BY 1;