快捷搜索: 长连接 前端 源码 pan

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

一道面试题,现场没写正确(默哀),回来记录一下

学生成绩表 :

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);

  1. 最容易想到的一种方式,先查询到每个课程的最高成绩,再通过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
  2. 第二种方式, 利用子查询语句 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得到的结果是一致的,至于那个效率高?

经验分享 程序员 微信小程序 职场和发展