Sql 查询学生成绩表中每个科目的最高分及对应科目和学生

2024-07-02 19:37| 来源: 网络整理| 查看: 265

一道面试题，现场没写正确（默哀），回来记录一下

学生成绩表：

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)

3条sql得到的结果是一致的，至于那个效率高？

【本文地址】

公司简介

联系我们