49，SQL训练之，力扣，1112. 每位学生的最高成绩

最新推荐文章于 2024-08-30 17:03:53 发布

凡梦_leo

最新推荐文章于 2024-08-30 17:03:53 发布

阅读量703

点赞数 17

分类专栏： SQL实战训练文章标签： leetcode 爬虫后端前端数据仓库数据库 mariadb

本文链接：https://blog.csdn.net/qq_55006020/article/details/140873509

版权

SQL实战训练专栏收录该内容

69 篇文章 0 订阅

订阅专栏

学习：知识的初次邂逅
复习：知识的温故知新

练习：知识的实践应用

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

表：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;  -- 呈现结果