獲取MySQL的表中每個userid最后一條記錄的方法

2024-07-24 12:46:07

字體：大中小

來源：轉載

供稿：網友

如下表：

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!