如下表：

CREATE TABLE `t1` ( `userid` int(11) DEFAULT NULL, `atime` datetime DEFAULT NULL, KEY `idx_userid` (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8； CREATE TABLE `t1` ( `userid` int(11) DEFAULT NULL, `atime` datetime DEFAULT NULL, KEY `idx_userid` (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8；

数据如下：

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!

【获取MySQL的表中每个userid最后一条记录的方法】相关文章：

★ 在MySQL中增添新用户权限的方法

★ MySQL的23个需要注意的地方

★ MySQL将表a中查询的数据插入到表b中

★ MySQL最常见的操作语句小结

★ MySQL数据库备份与恢复方法

★ MySQL创建用户与授权方法

★ MySQL随机查询记录的效率测试分析

★ MySQL中使用case when 语句实现多条件查询的方法

★ MySQL数据库中拷贝数据表的方法

★ MySQL添加删除主键的方法