講解mysql group by 組內(nèi)排序的方法

mysql的group by語法可以根據(jù)指定的規(guī)則對數(shù)據(jù)進(jìn)行分組，分組就是將一個數(shù)據(jù)集劃分成若干個小區(qū)域，然后再針對若干個小區(qū)域進(jìn)行數(shù)據(jù)處理。本文將介紹mysql使用group by分組時，實現(xiàn)組內(nèi)排序的方法。

mysql的group by語法可以對數(shù)據(jù)進(jìn)行分組，但是分組后的數(shù)據(jù)并不能進(jìn)行組內(nèi)排序。
例如一個評論表有多個用戶評論，需要獲取每個用戶最后評論的內(nèi)容。

創(chuàng)建測試數(shù)據(jù)表及數(shù)據(jù)

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表中，每個用戶最后評論的內(nèi)容就是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  
+----+---------+---------+---------------------+---------------------+

可以看到結(jié)果，分組后只會返回分組內(nèi)的第一條數(shù)據(jù)。因為group by語法沒有進(jìn)行組內(nèi)排序的功能，只會按mysql默認(rèn)的排序顯示。
如何才能對group by分組內(nèi)的數(shù)據(jù)進(jìn)行排序了，這個需要根據(jù)不同的需求處理。

1.id最大的，評論時間肯定最新

這種情況我們可以使用id代替時間去搜尋并組內(nèi)排序，使用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與評論時間沒有關(guān)系，id大的評論時間可能不是最新

這種情況我們就需要使用max(addtime)來獲取最新的評論，但因為不同用戶的評論時間有可能相同，因此還需要加多user_id這個條件去查詢。

重新創(chuàng)建測試數(shù)據(jù)

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  
+----+---------+---------+---------------------+---------------------+

符合條件的應(yīng)該是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可以減少外層的數(shù)據(jù)集。
where user_id is not null 可以使group by user_id時使用索引。

本篇文章講解了mysql group by 組內(nèi)排序的方法 ，更多相關(guān)內(nèi)容請關(guān)注php中文網(wǎng)。

相關(guān)推薦：

如何利用php 來反射API獲取類信息

詳解在mysql查詢時，offset過大影響性能的原因與優(yōu)化方法

關(guān)于php使用正則去除寬高樣式的方法

以上就是講解mysql group by 組內(nèi)排序的方法的詳細(xì)內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！