Sql 成绩表中每个科目的最高分和学生

SELECT MAX(grade) FROM grade_table GROUP BY name;

学生成绩表 ：

CREATE TABLEtabscore(

idint(11) NOT NULL AUTO_INCREMENT,

studentsvarchar(255) DEFAULT NULL,

coursevarchar(255) DEFAULT NULL,

scorefloat(3,1) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTOtabscoreVALUES (‘1’, ‘张三’, ‘语文’, ‘75.0’);

INSERT INTOtabscoreVALUES (‘2’, ‘张三’, ‘数学’, ‘80.0’);

INSERT INTOtabscoreVALUES (‘3’, ‘李四’, ‘语文’, ‘60.0’);

INSERT INTOtabscoreVALUES (‘4’, ‘李四’, ‘数学’, ‘99.0’);

INSERT INTOtabscoreVALUES (‘5’, ‘王五’, ‘语文’, ‘85.0’);

INSERT INTOtabscoreVALUES (‘6’, ‘王五’, ‘数学’, ‘99.0’);

最容易想到的一种方式，先查询到每个课程的最高成绩，再通过join连表查询

select * from

(select course, MAX(score) as maxScore from tabscore GROUP BY course) t2

join

tabscore

on tabscore.course = t2.course and tabscore.score = t2.maxScore

第二种方式， 利用子查询语句

select * from tabscore

where score =

(SELECT MAX(score) as maxscore from tabscore tb2 where tb2.course = tabscore.course)

或者 having

select * from tabscore

where score =

(SELECT MAX(score) as maxscore from tabscore tb2 GROUP BY course having tb2.course = tabscore.course)