group by 误区

需求：
找出scorelist表同一班级类型(class)中，最大的分数(score)，输出结果，分数最大的学生姓名，班级和分数。scorelist表的字段为（id,name,class,score）；
需求1、最大score有并列的每个class只返回一行；
需求2、最大score有并列的，输出所有并列结果；

创建表：

CREATE TABLE `scorelist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32)  not NULL default '',
  `class` varchar(32)  not NULL default '',
  `score` int(11) not NULL default 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

插入数据：

mysql> insert into scorelist values
(null,'zhao','A',89),(null,'qian','A',100),
(null,'sun','A',99),(null,'li','A',100),
(null,'zhou','A',90),(null,'wu','B',95),
(null,'zheng','B',90),(null,'wang','B',99),
(null,'feng','B',88),(null,'chen','B',98);

全表数据如下：

image.png

需求1：最大score有并列的每个class只返回一行

SELECT name,class, MAX(score)  FROM scorelist GROUP BY class;

结果：

image.png
从全表数据可以看到，此处的学生姓名的查询结果应该是wang
image.png
说明：这样的语句是非SQL标准的，虽然能够在MySQL数据库中执行成功，但返回的却是未知的；（如果sql_mode开启了only_full_group_by，则不会执行成功。）可能name字段会取class分组前的第一个的值，显然不是所需信息
参考：https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html

优化：

SELECT s.name,s.class,s.score FROM scorelist s,
(SELECT class,MAX(score) as maxscore FROM scorelist GROUP BY class) m
WHERE s.class = m.class AND s.score = m.maxscore 

优化说明：
避免group by时会取首位的字段直接查询数据，然后将查询结果看作新表与原表进行内关联查询，得出最终结果集

结果：

image.png

结果说明：结果为需求2的结果，在查询语句直接加上GROUP BY class 即是需求1的结果，这里能直接GROUP BY class的原因是因为qian 和 li 数据相同，取那个都一样