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

来源：懂视网责编：小采时间：2020-11-09 21:00:46

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

获取MySQL的表中每个userid最后一条记录的方法:如下表： CREATE TABLE `t1` ( `userid` int(11) DEFAULT NULL, `atime` datetime DEFAULT NULL, KEY `idx_userid` (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8； CREATE TABLE `t1` ( `u

推荐度：

点击下载本文 文档为doc格式

导读获取MySQL的表中每个userid最后一条记录的方法:如下表： CREATE TABLE `t1` ( `userid` int(11) DEFAULT NULL, `atime` datetime DEFAULT NULL, KEY `idx_userid` (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8； CREATE TABLE `t1` ( `u

如下表：

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!