mySQL之group By的简单分析

张颢镡

　　1.测试数据:
create table `t_class` (　　`id` bigint (11),
　　`name` varchar (150),
　　`age` int (11),
　　`class` int (11)
　　
);
　　
insert into `t_class` (`id`, `name`, `age`, `class`) values('1','tom1','22','1');
　　
insert into `t_class` (`id`, `name`, `age`, `class`) values('2','tom2','32','1');
　　
insert into `t_class` (`id`, `name`, `age`, `class`) values('4','jerry2','33','2');
　　
insert into `t_class` (`id`, `name`, `age`, `class`) values('5','hanchao','11','3');
　　
insert into `t_class` (`id`, `name`, `age`, `class`) values('6','hanchao2','30','3');
　　
insert into `t_class` (`id`, `name`, `age`, `class`) values('7','jerry3','32','2');
　　
insert into `t_class` (`id`, `name`, `age`, `class`) values('8','jerry4','23','2');
　　
insert into `t_class` (`id`, `name`, `age`, `class`) values('9','jerry1','33','2');
　　2.需求:查找每个班年龄最大的学生的信息。
　　很明显，我们会用到group by，
　　eg：
SELECT id,NAME,MAX(age),class FROM t_class GROUP BY class;【错误的写法！！】　　但是，这是错误的写法！！
　　当然，我们也可以这么写：
SELECT * FROM (　　SELECT * FROM t_class ORDER BY age DESC) AS b
　　GROUP BY class;
　　但是，我不建议这么写，因为这么写有些不可预知的事情发生。我们先order by 再group by ,结果可能不是取的年龄最大的学生信息，具体原因，我还在寻找中,当然你知道原因也可以告诉我，证据如下:

　　所以，我们可以这么去写：
/**方法1：**/　　
SELECT * FROM t_class t WHERE t.age = (SELECT MAX(age) FROM t_class WHERE t.class = class) ORDER BY class;
　　

　　
/**方法2：**/
　　
SELECT t_class.id,t_class.name,t_class.age,t_class.class FROM t_class
　　
INNER JOIN (SELECT class,MAX(age) AS maxAge
　　
FROM t_class GROUP BY class)t ON (t.class = t_class.class AND t_class.age = t.maxAge);
　　至于那个方法的效率高，你可以自己测试一下！
　　可以读一下这篇文字，可以更好的理解group by：http://www.cnblogs.com/wiseblog/articles/4475936.html
　　http://blog.csdn.net/john_hongming/article/details/42742965
　　http://www.th7.cn/db/mysql/201502/91713.shtml
　　http://yueliangdao0608.blog.51cto.com/397025/81278
　　说明：
　　（1）      在SQL标准中，作为from后的子表或者子查询，其中的order by 无效，会被忽略，这一点在mysql5.7/mariadb 10中得到修正，之前的版本并不严格：

　　（2）      group by对数据分组后，除被group by 的column之外，会选择任意一条数据返回，也就是说，被group by的其他列是无序的。
　　（3）      经过测试，发现在mysql 5.7和mariadb 10.*版本中，对相同的group by column，取值是根据数据写入的顺序来排序，即id顺序；
　　（4）      在group by 之后加order by子句对返回值无效，因只order by只对返回集合排序；
　　（5）      参考资料

　　Mariadb 10 :

测试内容：　　
set namesutf8;
　　
createtable testorder(id int not null,no int not null,name char(10) not null,primary key(id))engine=innodb;
　　
insertinto testorder values (1,1,'Mike'),(2,2,'John'),(3,3,'wyett'),(4,4,'Herry'),(5,5,'Mike'),(6,1,'John'),(7,2,'John'),(8,1,'Mike'),(9,1,'Mike');
　　
select* from testorder;

select* from testorder order by no desc;

select* from (select id,no,name from testorder order by no desc)a group by a.name;　　group by的默认排序是asc，因此name列是按照首字母升序排序的，对name相同的值
　　测试1：
　　Mariadb10：

　　结果：.取值是按照id从小到大的顺序来的，对相同的no值，按写入的物理排序进行的；
　　测试2：Percona5.5
　　测试3：Percona5.1
　　测试4：MySQL5.6，关闭sql_mode=ONLY_FULL_GROUP_BY
　　测试5：MySQL5.1,
　　(5.1的测试结果）

　　结果：
　　1.对name相同的值，按照no从大到小的顺序排列，对no相同的值，按物理排序从小到大取值；
　　测试6：mysql5.7，关闭sql_mode=ONLY_FULL_GROUP_BY

　　结果：取值是按照id从小到大的顺序来的，对相同的no值，按写入的物理排序进行的；
　　测试7:mariadb 10，对no加desc排序

　　alter tabletestorder add index>
　　select* from (select>

　　结果：desc是按照写入顺序也就是索引顺序排序，不是按照大小排序