The structure of SQL shows below:
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');
表:Enrollments
+-----------------------+--------+
| Column Name | Type |
+---------------------+----------+
| student_id | int |
| course_id | int |
| grade | int |
+----------------------+---------+
(student_id, course_id) 是该表的主键。
编写一个 SQL 查询,查询每位学生获得的最高成绩和它所对应的科目,若科目成绩并列,取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入:
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 |
下面是正确输出代码:
SELECT
student_id,
course_id,
grade
FROM
(SELECT
student_id,
course_id,
grade,
DENSE_RANK() OVER (PARTITION BY student_id ORDER BY grade DESC ,course_id) rk
FROM
enrollments) t where rk = 1
;
本题关键之处是用了窗口函数:通常在需要对数据进行分组汇总计算时使用,因此与聚集函数有一定的相似性。但与聚集函数不同的是,聚集函数通过对数据进行分组,仅能够输出分组汇总结果,而原始数据则无法展现在结果中。而窗口函数则可以同时将原始数据和聚集分析结果同时显示出来。
DENSE_RANK()函数是属于在窗口函数中为排序函数,此函数特点它为分区或结果集中的每一行分配排名,而排名值没有间隙。用它则显示1,1,2,3,4
此外,窗口函数的语法为如下,并且必须起别名
over (partition by...)
Reference:力扣