MySQL分组取分组中最大值 求解决...

Question

表结构CREATE TABLE grade(num INT(10) NOT NULL,course VARCHAR(10) NOT NULL,score FLOAT);表数据INSERT INTO grade VALUES(1001, '数学', 80), (1001, '语文', 90), (1001, '英语', 90), (1001, '计算机', 95);INSERT INTO grade VALUES(1002, '数学', 88), (1002, '语文', 90), (1002, '英语', 89), (1002, '计算机', 90);INSERT INTO grade VALUES(1003, '数学', 80), (1003, '语文', 98), (1003, '英语', 85), (1003, '计算机', 95);查询语句SELECT course, MAX(score), any_value(num) FROM grade GROUP BY course;查出来的数据不对，后面是1001，跟成绩对应不上course MAX(score) any_value(num)数学 88 1001语文 98 1001英语 90 1001计算机 95 1001求解决

Answer 1

SELECT 
S.course,S.score,S.num
FROM (SELECT course,MAX(score) AS score FROM grade GROUP BY course) AS C
join grade AS S 
ON S.score=C.score AND S.course=C.course