如下表：
?
| 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!
點擊查看更多內容
為 TA 點贊
評論
評論
共同學習，寫下你的評論
評論加載中...
作者其他優(yōu)質文章
正在加載中
感謝您的支持，我會繼續(xù)努力的～
掃碼打賞，你說多少就多少
贊賞金額會直接到老師賬戶
支付方式
打開微信掃一掃，即可進行掃碼打賞哦