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得到的结果是一致的,至于那个效率高?

 

 

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页