- 学习:知识的初次邂逅
- 复习:知识的温故知新
练习:知识的实践应用
目录
一,原题力扣链接
二,题干
表:
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');
四,分析
题解:
表:招生人数表字段:学生id,学生科目,学生成绩
要求,每个学生的最高成绩,如果多个科目的成绩相同,以科目排序
思路
第一步,以学生分组,以成绩排序 降序, 开窗 采用rank 因为可能有多个科目的成绩并列第一
第二步,在根据 学生分组,以学科排序,做一个新的排名
第三步,提取rn2 为1的数据 最终呈现给用户
五,SQL解答
with t1 as (
select student_id, course_id, grade,
rank() over (partition by student_id order by grade desc ) ro1 -- 开窗 拿到以学生id 分组 以分数排序
from enrollments
),t2 as (
select student_id, course_id, grade, ro1,
row_number() over (partition by student_id order by course_id) ro2 -- 开窗 拿到以学生id分组 以学科排序
from t1 where t1.ro1=1
),t3 as (
select student_id, course_id, grade, ro1, ro2 -- 三个表有并列 所以这个表取并列 拿第一
from t2 where t2.ro2=1
)
select student_id, course_id, grade from t3; -- 呈现结果
六,验证
七,知识点总结
- rank开窗函数的运用 排名允许并列
- row_number开窗函数的运用,排名不并列
- 分组求第一,首选开窗
- 分组求并列第一在求第一,再开一边窗!
- 学习:知识的初次邂逅
- 复习:知识的温故知新
练习:知识的实践应用