题目描述
(通过次数14,777 | 提交次数22,508,通过率65.65%)
表: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 | +------------+-----------+-------+ 来源:力扣(LeetCode) 链接:https://leetcode.cn/problems/highest-grade-for-each-student
//测试数据 Create table If Not Exists Enrollments (student_id int, course_id int, grade int); 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');
解题思路
这是一道非常典型的开窗函数使用场景的题目。
题目要求:查询每位学生获得的最高成绩和它所对应的科目。若科目成绩并列,取course_id最小的一门。
本质上来说,这是一个去重的操作。
去重操作有两点需要明确:
第一:按什么字段去重(去重的KEY)?
题目要求:查询每位学生的数据,一个学生一条件结果。那么,很明显。这里的KEY是student_id。
在使用开窗函数时,KEY值被写在partition by的后面。
第二:当多条记录的KEY相同时,按什么规则取出唯一的一条(去重规则)?
题目要求:一个学生有多条数据时,查询出成绩最高的那一条。如果存在多门课程并列第一的情况时,取课程ID(course_id)的值最小的那一条。
在使用开窗函数时,去重规则写在order by的后面。
参考SQL
未特别说明的情况下,参考SQL为基于MySQL8.0实现。
select student_id, course_id, grade from ( select student_id, course_id, grade, row_number() over(partition by student_id order by grade desc,course_id) rn from Enrollments )a where rn = 1;
本站所有内容均为原创,本站保留所有权利。仅允许非商业用途的转载,但必须注明来源网站、作者、来源链接!否则,由此造成的一切后果,由转载方承担!
干货分享、技术提升、面试笔试、学习交流,欢迎关注公众号:xuesql。QQ学习交流群:209942678。