SELECT MAX(grade) FROM grade_table GROUP BY name;
学生成绩表 :
CREATE TABLE tabscore
(
id
int(11) NOT NULL AUTO_INCREMENT,
students
varchar(255) DEFAULT NULL,
course
varchar(255) DEFAULT NULL,
score
float(3,1) DEFAULT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO tabscore
VALUES (‘1’, ‘张三’, ‘语文’, ‘75.0’);
INSERT INTO tabscore
VALUES (‘2’, ‘张三’, ‘数学’, ‘80.0’);
INSERT INTO tabscore
VALUES (‘3’, ‘李四’, ‘语文’, ‘60.0’);
INSERT INTO tabscore
VALUES (‘4’, ‘李四’, ‘数学’, ‘99.0’);
INSERT INTO tabscore
VALUES (‘5’, ‘王五’, ‘语文’, ‘85.0’);
INSERT INTO tabscore
VALUES (‘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)