如下表：
?
| 1234567891011 |
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；
|
数据如下：
?
| 1234567891011121314151617181920212223242526272829 |
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操作之类的．
给一个简方法：
?
| 123456789101112131415161718192021 |
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!
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/912/viewspace-2811097/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
