题目来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/highest-grade-for-each-student
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
本人思路:
– 1、先选择出每个学生最高的成绩,构成临时表temp
– 2、将原有的Enrollments 表内连接上temp,并且筛选出成绩和最高成绩相等的学生信息,构成表temp1
– 3、最后处理‘科目成绩并列,取 course_id 最小的一门’,先按学生id进行分组,最后使用min最小聚合函数
select temp1.student_id,min(temp1.course_id) as course_id ,temp1.grade
from(
select e.student_id as student_id, e.course_id as course_id ,e.grade as grade
from Enrollments as e inner join(
select student_id,max(grade)as max_grade
from Enrollments
group by student_id
)temp
on e.student_id = temp.student_id
where e.grade = temp.max_grade
)temp1
group by temp1.student_id
order by temp1.student_id