|
原文：深度分析mysql GROUP BY 與 ORDER BY、mysql取出每個(gè)分組中最新的記錄、mysql 分組取最新的一條記錄(整條記錄)
1、建表、插入測(cè)試數(shù)據(jù)
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`category_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
insert into `test`(`id`,`name`,`category_id`,`date`) values (1,'aaa',1,'2010-06-10 19:14:37'),(2,'bbb',2,'2010-06-10 19:14:55'),(3,'ccc',1,'2010-06-10 19:16:02'),(4,'ddd',1,'2010-06-10 19:16:15'),(5,'eee',2,'2010-06-10 19:16:35');
我現(xiàn)在需要取出每個(gè)分類(lèi)中最新的內(nèi)容
select * from test group by category_id order by date
結(jié)果如下
明顯。這不是我想要的數(shù)據(jù)，原因是msyql已經(jīng)的執(zhí)行順序是：
寫(xiě)的順序：select … from… where…. group by… having… order by..
執(zhí)行順序：from… where…group by… having…. select … order by…
所以在order by拿到的結(jié)果里已經(jīng)是分組的完的最后結(jié)果。
由from到where的結(jié)果如下的內(nèi)容：
到group by時(shí)就得到了根據(jù)category_id分出來(lái)的多個(gè)小組
到了select的時(shí)候，只從上面的每個(gè)組里取第一條信息結(jié)果會(huì)如下
即使order by也只是從上面的結(jié)果里進(jìn)行排序。并不是每個(gè)分類(lèi)的最新信息。
2、方法
下面介紹兩種方法，一種是通過(guò)子查詢(xún)，一種是通過(guò)group_concat函數(shù)來(lái)實(shí)現(xiàn)。
2.1 子查詢(xún)解決方案
先將數(shù)據(jù)按照日期倒序排序（日期最新的在最前面），然后在group，這樣每個(gè)分類(lèi)的第一條肯定是日期最新的。
select * from (select * from `test` order by `date` desc) `temp` group by category_id order by `date` desc
2.2 通過(guò)group_concat函數(shù)
①group_concat函數(shù)
group_concat( [DISTINCT] 要連接的字段 [Order BY 排序字段 ASC/DESC] [Separator '分隔符’] )
作用：將要連接的字段按照排序字段的順序用分隔符連起來(lái)顯示，默認(rèn)分隔符是”，”。
如：select group_concat(id order bydatedesc) fromtestgroup by category_id
按照時(shí)間排序?qū)d連接起來(lái)，第一個(gè)一定是時(shí)間最新的。
②substring_index
substring_index(str,delim,count)，str:要處理的字符串、delim:分隔符、count:計(jì)數(shù)
例子：
SELECT SUBSTRING_INDEX('www.test.com','.',1);
結(jié)果是：www
SELECT SUBSTRING_INDEX('www.test.com','.',2)
結(jié)果是：www.test
SELECT SUBSTRING_INDEX('www.test.com','.',-2);
結(jié)果為：test.com
也就是說(shuō)，如果count是正數(shù)，那么就是從左往右數(shù)，第N個(gè)分隔符的左邊的全部?jī)?nèi)容，相反，如果是負(fù)數(shù)，那么就是從右邊開(kāi)始數(shù)，第N個(gè)分隔符右邊的所有內(nèi)容。
③解析
GROUP_CONCAT將group by 后的id排序后連接起來(lái)，SUBSTRING_INDEX取得每行（每個(gè)分類(lèi)）的第一個(gè)。
SELECT * FROM `test` WHERE id IN(SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY `date` DESC),',',1) FROM `test` GROUP BY category_id ) ORDER BY `date` DESC;
2、3 其他方法
一般id越大的時(shí)間越近，可以先選出各個(gè)分類(lèi)最大的id，然后in。
SELECT MAX(id) AS id,category_id,MAX(DATE) FROM test GROUP BY category_id;
SELECT * FROM test WHERE id IN (SELECT MAX(id) FROM test GROUP BY category_id);
連表，選擇出每個(gè)分類(lèi)的id和最大時(shí)間作為一個(gè)臨時(shí)表，然后原表和臨時(shí)表連接，條件是分類(lèi)id和時(shí)間相等。
SELECT * FROM test AS a,
(SELECT category_id, MAX(DATE) AS `date` FROM test AS b GROUP BY category_id)
AS b WHERE a.category_id=b.category_id AND a.date = b.date
|
