mysql的group by语法能够依据指定的划定规矩对数据举行分组，分组就是将一个数据集划分红若干个小区域，然后再针对若干个小区域举行数据处置惩罚。本文将引见mysql运用group by分组时，完成组内排序的要领。
相干mysql视频教程引荐：《mysql教程》
mysql的group by语法能够对数据举行分组，然则分组后的数据并不能举行组内排序。
比方一个批评表有多个用户批评，须要猎取每一个用户末了批评的内容。
建立测试数据表及数据
CREATE TABLE `comment` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `content` varchar(200) NOT NULL, `addtime` datetime NOT NULL, `lastmodify` datetime NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `addtime` (`addtime`), KEY `uid_addtime` (`user_id`,`addtime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `comment` (`id`, `user_id`, `content`, `addtime`, `lastmodify`) VALUES(1, 1, '批评1', '2017-05-17 00:00:00', '2017-05-17 00:00:00'), (2, 1, '批评2', '2017-05-17 00:00:01', '2017-05-17 00:00:01'), (3, 2, '批评1', '2017-05-17 00:00:02', '2017-05-17 00:00:02'), (4, 2, '批评2', '2017-05-17 00:00:03', '2017-05-17 00:00:03'), (5, 3, '批评1', '2017-05-17 00:00:04', '2017-05-17 00:00:04'), (6, 1, '批评3', '2017-05-17 00:00:05', '2017-05-17 00:00:05'), (7, 4, '批评1', '2017-05-17 00:00:06', '2017-05-17 00:00:06'), (8, 4, '批评2', '2017-05-17 00:00:07', '2017-05-17 00:00:07'), (9, 4, '批评3', '2017-05-17 00:00:08', '2017-05-17 00:00:08'), (10, 4, '批评4', '2017-05-17 00:00:09', '2017-05-17 00:00:09'), (11, 3, '批评2', '2017-05-17 00:00:10', '2017-05-17 00:00:10');select * from comment;+----+---------+---------+---------------------+---------------------+| id | user_id | content | addtime | lastmodify | +----+---------+---------+---------------------+---------------------+| 1 | 1 | 批评1 | 2017-05-17 00:00:00 | 2017-05-17 00:00:00 | | 2 | 1 | 批评2 | 2017-05-17 00:00:01 | 2017-05-17 00:00:01 | | 3 | 2 | 批评1 | 2017-05-17 00:00:02 | 2017-05-17 00:00:02 | | 4 | 2 | 批评2 | 2017-05-17 00:00:03 | 2017-05-17 00:00:03 | | 5 | 3 | 批评1 | 2017-05-17 00:00:04 | 2017-05-17 00:00:04 | | 6 | 1 | 批评3 | 2017-05-17 00:00:05 | 2017-05-17 00:00:05 | | 7 | 4 | 批评1 | 2017-05-17 00:00:06 | 2017-05-17 00:00:06 | | 8 | 4 | 批评2 | 2017-05-17 00:00:07 | 2017-05-17 00:00:07 | | 9 | 4 | 批评3 | 2017-05-17 00:00:08 | 2017-05-17 00:00:08 | | 10 | 4 | 批评4 | 2017-05-17 00:00:09 | 2017-05-17 00:00:09 | | 11 | 3 | 批评2 | 2017-05-17 00:00:10 | 2017-05-17 00:00:10 | +----+---------+---------+---------------------+---------------------+
在comment表中，每一个用户末了批评的内容就是id为6,4,11,10的纪录。
运用group by查询
select * from comment group by user_id; +----+---------+---------+---------------------+---------------------+| id | user_id | content | addtime | lastmodify | +----+---------+---------+---------------------+---------------------+| 1 | 1 | 批评1 | 2017-05-17 00:00:00 | 2017-05-17 00:00:00 | | 3 | 2 | 批评1 | 2017-05-17 00:00:02 | 2017-05-17 00:00:02 | | 5 | 3 | 批评1 | 2017-05-17 00:00:04 | 2017-05-17 00:00:04 || 7 | 4 | 批评1 | 2017-05-17 00:00:06 | 2017-05-17 00:00:06 | +----+---------+---------+---------------------+---------------------+
能够看到效果，分组后只会返回分组内的第一条数据。由于group by语法没有举行组内排序的功用，只会按mysql默许的排序显现。
怎样才能对group by分组内的数据举行排序了，这个须要依据差别的需求处置惩罚。
1.id最大的，批评时候一定最新
这类状况我们能够运用id替代时候去征采并组内排序，运用max(id)就能够猎取到每一个分组中最大的批评id(即最新的批评)
select * from comment where id in(select max(id) from comment group by user_id) order by user_id; +----+---------+---------+---------------------+---------------------+| id | user_id | content | addtime | lastmodify | +----+---------+---------+---------------------+---------------------+| 6 | 1 | 批评3 | 2017-05-17 00:00:05 | 2017-05-17 00:00:05 | | 4 | 2 | 批评2 | 2017-05-17 00:00:03 | 2017-05-17 00:00:03 | | 11 | 3 | 批评2 | 2017-05-17 00:00:10 | 2017-05-17 00:00:10 || 10 | 4 | 批评4 | 2017-05-17 00:00:09 | 2017-05-17 00:00:09 | +----+---------+---------+---------------------+---------------------+
2.id与批评时候没有关系，id大的批评时候能够不是最新
这类状况我们就须要运用max(addtime)来猎取最新的批评，但由于差别用户的批评时候有能够雷同，因而还须要加多user_id这个前提去查询。
从新建立测试数据
truncate table comment;INSERT INTO `comment` (`id`, `user_id`, `content`, `addtime`, `lastmodify`) VALUES(1, 1, '批评1', '2017-05-17 00:00:00', '2017-05-17 00:00:00'), (2, 1, '批评2', '2017-05-17 00:10:01', '2017-05-17 00:10:01'), (3, 2, '批评1', '2017-05-17 00:10:02', '2017-05-17 00:10:02'), (4, 2, '批评2', '2017-05-17 00:00:03', '2017-05-17 00:00:03'), (5, 3, '批评1', '2017-05-17 00:10:04', '2017-05-17 00:10:04'), (6, 1, '批评3', '2017-05-17 00:00:05', '2017-05-17 00:00:05'), (7, 4, '批评1', '2017-05-17 00:00:06', '2017-05-17 00:00:06'), (8, 4, '批评2', '2017-05-17 00:10:07', '2017-05-17 00:10:07'), (9, 4, '批评3', '2017-05-17 00:00:08', '2017-05-17 00:00:08'), (10, 4, '批评4', '2017-05-17 00:00:09', '2017-05-17 00:00:09'), (11, 3, '批评2', '2017-05-17 00:00:10', '2017-05-17 00:00:10');select * from comment;+----+---------+---------+---------------------+---------------------+| id | user_id | content | addtime | lastmodify | +----+---------+---------+---------------------+---------------------+| 1 | 1 | 批评1 | 2017-05-17 00:00:00 | 2017-05-17 00:00:00 | | 2 | 1 | 批评2 | 2017-05-17 00:10:01 | 2017-05-17 00:10:01 | | 3 | 2 | 批评1 | 2017-05-17 00:10:02 | 2017-05-17 00:10:02 | | 4 | 2 | 批评2 | 2017-05-17 00:00:03 | 2017-05-17 00:00:03 | | 5 | 3 | 批评1 | 2017-05-17 00:10:04 | 2017-05-17 00:10:04 | | 6 | 1 | 批评3 | 2017-05-17 00:00:05 | 2017-05-17 00:00:05 | | 7 | 4 | 批评1 | 2017-05-17 00:00:06 | 2017-05-17 00:00:06 | | 8 | 4 | 批评2 | 2017-05-17 00:10:07 | 2017-05-17 00:10:07 | | 9 | 4 | 批评3 | 2017-05-17 00:00:08 | 2017-05-17 00:00:08 | | 10 | 4 | 批评4 | 2017-05-17 00:00:09 | 2017-05-17 00:00:09 | | 11 | 3 | 批评2 | 2017-05-17 00:00:10 | 2017-05-17 00:00:10 | +----+---------+---------+---------------------+---------------------+
相符前提的应该是id为2,3,5,8的纪录
select a.* from comment as a right join (select user_id, max(addtime) as maxtime from comment where user_id is not null group by user_id) as b on a.user_id=b.user_id and a.addtime=b.maxtime order by a.user_id asc;+------+---------+---------+---------------------+---------------------+| id | user_id | content | addtime | lastmodify | +------+---------+---------+---------------------+---------------------+| 2 | 1 | 批评2 | 2017-05-17 00:10:01 | 2017-05-17 00:10:01 | | 3 | 2 | 批评1 | 2017-05-17 00:10:02 | 2017-05-17 00:10:02 | | 5 | 3 | 批评1 | 2017-05-17 00:10:04 | 2017-05-17 00:10:04 | | 8 | 4 | 批评2 | 2017-05-17 00:10:07 | 2017-05-17 00:10:07 | +------+---------+---------+---------------------+---------------------+
运用right join能够削减外层的数据集。
where user_id is not null 能够使group by user_id时运用索引。
本篇文章解说了mysql group by 组内排序的要领 ，更多相干内容请关注ki4网。
相干引荐：
怎样应用php 来反射API猎取类信息
详解在mysql查询时，offset过大影响机能的缘由与优化要领
关于php运用正则去除宽高款式的要领
以上就是解说mysql group by 组内排序的要领的细致内容，更多请关注ki4网别的相干文章！
