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

最新推荐文章于 2025-04-21 12:35:14 发布

原创最新推荐文章于 2025-04-21 12:35:14 发布 · 6.1k 阅读

13 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql

mysql 专栏收录该内容

20 篇文章

订阅专栏

博客围绕MySQL学生成绩表展开，给出了创建表和插入数据的语句。重点介绍了查询每个课程最高成绩的方法，包括先查询最高成绩再连表查询，以及利用子查询语句和having子句进行查询。

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)