获取MySQL的表中每个userid最后一条记录的方法

获取MySQL的表中每个userid最后一条记录的方法,并且针对userid不唯一的情况

如下表：

				?

									CREATE TABLE `t1` ( 

									`userid` int(11) DEFAULT NULL, 

									`atime` datetime DEFAULT NULL, 

									KEY `idx_userid` (`userid`) 

									) ENGINE=InnoDB DEFAULT charset=utf-8； 

									CREATE TABLE `t1` ( 

									`userid` int(11) DEFAULT NULL, 

									`atime` datetime DEFAULT NULL, 

									KEY `idx_userid` (`userid`) 

									) ENGINE=InnoDB DEFAULT charset=utf-8；

数据如下：

				?

									MySQL> select * from t1; 

									+--------+---------------------+ 

									| userid | atime | 

									+--------+---------------------+ 

									| 1 | 2013-08-12 11:05:25 | 

									| 2 | 2013-08-12 11:05:29 | 

									| 3 | 2013-08-12 11:05:32 | 

									| 5 | 2013-08-12 11:05:34 | 

									| 1 | 2013-08-12 11:05:40 | 

									| 2 | 2013-08-12 11:05:43 | 

									| 3 | 2013-08-12 11:05:48 | 

									| 5 | 2013-08-12 11:06:03 | 

									+--------+---------------------+ 

									8 rows in set (0.00 sec) 

									MySQL> select * from t1; 

									+--------+---------------------+ 

									| userid | atime | 

									+--------+---------------------+ 

									| 1 | 2013-08-12 11:05:25 | 

									| 2 | 2013-08-12 11:05:29 | 

									| 3 | 2013-08-12 11:05:32 | 

									| 5 | 2013-08-12 11:05:34 | 

									| 1 | 2013-08-12 11:05:40 | 

									| 2 | 2013-08-12 11:05:43 | 

									| 3 | 2013-08-12 11:05:48 | 

									| 5 | 2013-08-12 11:06:03 | 

									+--------+---------------------+ 

									8 rows in set (0.00 sec)

其中userid不唯一，要求取表中每个userid对应的时间离现在最近的一条记录．初看到一个这条件一般都会想到借用临时表及添加主建借助于join操作之类的．
给一个简方法：

			
									MySQL> select userid,substring_index(group_concat(atime order by atime desc),",",1) as atime from t1 group by userid; 

									+--------+---------------------+ 

									| userid | atime | 

									+--------+---------------------+ 

									| 1 | 2013-08-12 11:05:40 | 

									| 2 | 2013-08-12 11:05:43 | 

									| 3 | 2013-08-12 11:05:48 | 

									| 5 | 2013-08-12 11:06:03 | 

									+--------+---------------------+ 

									4 rows in set (0.03 sec) 

									MySQL> select userid,substring_index(group_concat(atime order by atime desc),",",1) as atime from t1 group by userid; 

									+--------+---------------------+ 

									| userid | atime | 

									+--------+---------------------+ 

									| 1 | 2013-08-12 11:05:40 | 

									| 2 | 2013-08-12 11:05:43 | 

									| 3 | 2013-08-12 11:05:48 | 

									| 5 | 2013-08-12 11:06:03 | 

									+--------+---------------------+ 

									4 rows in set (0.03 sec)

Good luck!