mysql的group by語法可以根據(jù)指定的規(guī)則對數(shù)據(jù)進(jìn)行分組，分組就是將一個數(shù)據(jù)集劃分成若干個小區(qū)域，然后再針對若干個小區(qū)域進(jìn)行數(shù)據(jù)處理。本文將介紹mysql使用group by分組時，實現(xiàn)組內(nèi)排序的方法。
相關(guān)mysql視頻教程推薦：《mysql教程》
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)推薦：
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載。
THE END
喜歡就支持一下吧
相關(guān)推薦
.png)
