# Write your MySQL query statement below
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 | +------------+-----------+-------+
原站题解
mysql 解法, 执行用时: 396 ms, 内存消耗: 0 B, 提交时间: 2023-10-15 22:27:32
select student_id, course_id, grade from ( select student_id, course_id, grade, rank() over (partition by student_id order by grade desc, course_id asc ) as ranking from Enrollments ) t1 where ranking = 1 order by student_id