mysql 分組查詢

一、表結構

--
-- 表的結構 `client_status`
--

CREATE TABLE IF NOT EXISTS `client_status` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `client_id` mediumint(8) unsigned NOT NULL,
  `addtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
) 

--
-- 轉存表中的資料 `client_status`
--

INSERT INTO `client_status` (`id`, `client_id`, `addtime`) VALUES
(3, 1,  '2012-03-26 10:32:30'),
(4, 1,  '2012-03-26 10:30:31'),
(5, 1,  '2012-03-26 10:30:32'),
(6, 2,  '2012-03-26 10:30:33'),
(7, 2,  '2012-03-26 10:30:34'),
(8, 4,  '2012-03-26 10:30:35'),
(9, 4,  '2012-03-26 10:30:36'),
(10, 3,  '2012-03-26 10:30:37'),
(11, 3,  '2012-03-26 10:30:38');

二、目的

按client_id分組，查詢每個client_id中離目前時間最近的記錄。

查詢結果示意

id	client_id	addtime
3	1	2012-03-26 10:32:30
7	2	2012-03-26 10:30:34
11	3	2012-03-26 10:30:38
9	4	2012-03-26 10:30:36

三、解決方案

方案1：

SELECT * FROM (SELECT * FROM client_status ORDER BY addtime DESC )a GROUP BY client_id
explain分析

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
2	DERIVED	client_status	ALL	NULL	NULL	NULL	NULL	9	Using filesort

方案2：

SELECT * FROM client_status a WHERE NOT EXISTS (SELECT 1 FROM client_status WHERE a.client_id = client_id AND a.addtime < addtime)

explain分析

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	9	Using where
2	DEPENDENT SUBQUERY	client_status	ref	client_id	client_id	3	pdmon.a.client_id	1	Using where

方案3：

SELECT * FROM `client_status` WHERE addtime IN (SELECT max( addtime ) FROM client_status GROUP BY client_id )

explain分析

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	client_status	ALL	NULL	NULL	NULL	NULL	9	Using where
2	DEPENDENT SUBQUERY	client_status	index	NULL	client_id	3	NULL	1

如果有更高效的SQL，歡迎回複！