描述: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 | +------------+-----------+-------+
数据准备:
Create table If Not Exists Enrollments (student_id int, course_id int, grade int)
Truncate table Enrollments
insert into Enrollments (student_id, course_id, grade) values ('2', '2', '95')
insert into Enrollments (student_id, course_id, grade) values ('2', '3', '95')
insert into Enrollments (student_id, course_id, grade) values ('1', '1', '90')
insert into Enrollments (student_id, course_id, grade) values ('1', '2', '99')
insert into Enrollments (student_id, course_id, grade) values ('3', '1', '80')
insert into Enrollments (student_id, course_id, grade) values ('3', '2', '75')
insert into Enrollments (student_id, course_id, grade) values ('3', '3', '82');
分析:
①根据每个学生分组 根据分数降序 分数相同根据课程号升序排名
select *,
row_number()
over (partition by student_id order by grade desc,course_id) r1
from Enrollments
②取排名为1 的数据
select *, row_number() over (partition by student_id order by grade desc,course_id) r1 from Enrollments;
代码:
with t1 as (select *,
row_number()
over (partition by student_id order by grade desc,course_id) r1
from Enrollments)
select student_id, course_id, grade
from t1
where r1 = 1;
总结:
设置一个排名开窗函数 使用row_number 避免重复 按照分数优先 然后课程其次的优先级排名