旗下导航:搜·么
当前位置:网站首页 > MySQL教程 > 正文

解说mysql group by 组内排序的要领【MySQL教程】,mysql,group-by

作者:搜教程发布时间:2019-11-27分类:MySQL教程浏览:65评论:0


导读:mysql的groupby语法能够依据指定的划定规矩对数据举行分组,分组就是将一个数据集划分红若干个小区域,然后再针对若干个小区域举行数据处置惩罚。本文将引见mysql运用gro...

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网别的相干文章!

标签:mysqlgroup-by


欢迎 发表评论: