1 题目描述
Enrollments表:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| student_id | int |
| course_id | int |
| grade | int |
+---------------+---------+
(student_id, course_id) 是该表的主键(具有唯一值的列的组合) grade 不会为 NULL
编写解决方案, 找出每位学生获得的最高成绩和它所对应的科目, 若科目成绩并列, 取 course_id 最小的一门, 查询结果需按 student_id 增序进行排序
2 测试用例
输入: 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 |
+------------+-----------+-------+
3 解题思路
- 将
student_id分组统计,grade倒序进行排名
select student_id, course_id, grade, rank() over (partition by student_id order by grade desc) as rk from Enrollments
查询结果
+----------+---------+-----+--+
|student_id|course_id|grade|rk|
+----------+---------+-----+--+
|1 |2 |99 |1 |
|1 |1 |90 |2 |
|2 |2 |95 |1 |
|2 |3 |95 |1 |
|3 |3 |82 |1 |
|3 |1 |80 |2 |
|3 |2 |75 |3 |
+----------+---------+-----+--+
rk = 1筛选出每个学生最高分数和对应的学科, 一个学生可能存在多个最高分
select e.student_id, course_id, e.grade, e.rk
from (select student_id, course_id, grade, rank() over (partition by student_id order by grade desc) as rk
from Enrollments) as e
where e.rk = 1
查询结果
+----------+---------+-----+--+
|student_id|course_id|grade|rk|
+----------+---------+-----+--+
|1 |2 |99 |1 |
|2 |2 |95 |1 |
|2 |3 |95 |1 |
|3 |3 |82 |1 |
+----------+---------+-----+--+
- 将
student_id 和 grade分组统计, 查找出最小的course_id, 并按照student_id升序排序
select e.student_id, min(course_id) as course_id, e.grade
from (select student_id, course_id, grade, rank() over (partition by student_id order by grade desc) as rk
from Enrollments) as e
where e.rk = 1
group by e.student_id, e.grade
order by e.student_id;
查询结果
+----------+---------+-----+
|student_id|course_id|grade|
+----------+---------+-----+
|1 |2 |99 |
|2 |2 |95 |
|3 |3 |82 |
+----------+---------+-----+
