SQL实战：每位学生的最高成绩

蔓蔓的数据空间 2022-04-06

2263

说明：题目来源为力扣(LeetCode)，本文主要记录笔者解题思路及发现的一些奇妙的解题思路

表：Enrollments

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| course_id     | int     |
| grade         | int     |
+---------------+---------+

(student_id, course_id) 是该表的主键。

编写一个 SQL 查询，查询每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取 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    |
+------------+-----------+-------+

一般思路1：内连接

select t1.student_id,min(t2.course_id) as course_id,t1.g as grade
from 
(select student_id,max(grade) as g
from Enrollments
group by student_id) t1
join
Enrollments t2
where t1.student_id=t2.student_id
and t1.g=t2.grade
group by t1.student_id
order by t1.student_id

一般思路2：子查询，在这里IN 2个字段

select student_id,min(course_id) course_id,grade
from Enrollments
where (student_id, grade)
in (select student_id A,MAX(grade) B
from Enrollments
group by student_id
)
group by student_id
group by student_id

奇妙思路：使用窗口函数，注：窗口函数在mysql8.0版本之后出现，低版本无法使用

select t.student_id,t.course_id,t.grade
from
(select student_id,course_id,grade,
rank() over w as r,
row_number() over w as rn
from Enrollments
window w as (partition by student_id order by grade desc,course_id)
)t
where t.r=1 and t.rn=1

窗口函数说明：

要使用窗口函数，需要使用OVER子句，一般有两种形式：

1.由对查询中其他地方定义的命名窗口的引用提供，如上例：window w as (partition by student_id order by grade desc,course_id)定义了窗口w，rank() over w as r中直接引用窗口w

2.在OVER子句中直接定义，如上例，可以去掉window w 这一行窗口定义，在over后直接定义：rank() over (partition by student_id order by grade desc,course_id) as r

partition by：分区字段

排名函数说明：

rank()：产生有间隙的等级，对重复项分配相同的排名值，下一排名+上一排名重复数，比如有2（重复数）个并列排名第1，那么下一排名就是3

dense_rank()：产生没有间隙的等级，对重复项分配相同的排名值，下一排名+1

row_number()：显示各分区行号

可参考官方示例：

mysql> SELECT
         val,
        ROW_NUMBER() OVER w AS 'row_number',
        RANK()       OVER w AS 'rank',
        DENSE_RANK() OVER w AS 'dense_rank'
       FROM numbers       WINDOW w AS (ORDER BY val);

查询结果：

+------+------------+------+------------+
| val  | row_number | rank | dense_rank |
+------+------------+------+------------+
|    1 |          1 |    1 |          1 |
|    1 |          2 |    1 |          1 |
|    2 |          3 |    3 |          2 |
|    3 |          4 |    4 |          3 |
|    3 |          5 |    4 |          3 |
|    3 |          6 |    4 |          3 |
|    4 |          7 |    7 |          4 |
|    4 |          8 |    7 |          4 |
|    5 |          9 |    9 |          5 |
+------+------------+------+------------+

数据库

文章转载自蔓蔓的数据空间，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL实战：每位学生的最高成绩

评论