题61:
根据下表编写一个 SQL 查询,查询每位学生获得的最高成绩和它所对应的科目,若科目成绩并列,取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。
其中:(student_id, course_id) 是该表的主键。
解题思路:
(1)首先获取每id最高成绩:select student_id,max(grade) from Enrollments group by student_id
(2)结合第一步找到对应的课程id:where (student_id,grade) in (select student_id,max(grade) from Enrollments group by student_id)
(3)根据min(course_id) 解决科目成绩并列情况。
代码如下:
select student_id,min(course_id) as course_id,grade
from Enrollments
where (student_id,grade) in
(select student_id,max(grade) from Enrollments group by student_id)
group by student_id
order by student_id;