2021-05-17

每位学生的最高成绩

需求一：查询每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。

展示效果：

student_id	average_years	grade
1	2	99
2	2	95
3	3	82

Create table If Not Exists 48_Enrollments (student_id int, course_id int, grade int);
Truncate table 48_Enrollments;
insert into 48_Enrollments (student_id, course_id, grade) values (2, 2, 95);
insert into 48_Enrollments (student_id, course_id, grade) values (2, 3, 95);
insert into 48_Enrollments (student_id, course_id, grade) values (1, 1, 90);
insert into 48_Enrollments (student_id, course_id, grade) values (1, 2, 99);
insert into 48_Enrollments (student_id, course_id, grade) values (3, 1, 80);
insert into 48_Enrollments (student_id, course_id, grade) values (3, 2, 75);
insert into 48_Enrollments (student_id, course_id, grade) values (3, 3, 82);

最终SQL:

SELECT
	t3.student_id,
	MAX(course_id) average_years,
	MAX(max)
FROM
	(	SELECT
	t1.student_id,
	t2.course_id,
	t1.max
FROM
	(SELECT 
	student_id,
	MAX(grade) max
	from 
	48_Enrollments
	GROUP BY
  student_id
	ORDER BY
	student_id) t1
	left join
	48_Enrollments  t2
on
	t1.max = t2.grade) t3
GROUP BY
	student_id
ORDER BY
	student_id