如下表:
CREATETABLE`t1`(
`userid`int(11)DEFAULTNULL,
`atime`datetimeDEFAULTNULL,
KEY`idx_userid`(`userid`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;
CREATETABLE`t1`(
`userid`int(11)DEFAULTNULL,
`atime`datetimeDEFAULTNULL,
KEY`idx_userid`(`userid`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;
数据如下:
MySQL>select*fromt1;
+--------+---------------------+
|userid|atime|
+--------+---------------------+
|1|2013-08-1211:05:25|
|2|2013-08-1211:05:29|
|3|2013-08-1211:05:32|
|5|2013-08-1211:05:34|
|1|2013-08-1211:05:40|
|2|2013-08-1211:05:43|
|3|2013-08-1211:05:48|
|5|2013-08-1211:06:03|
+--------+---------------------+
8rowsinset(0.00sec)
MySQL>select*fromt1;
+--------+---------------------+
|userid|atime|
+--------+---------------------+
|1|2013-08-1211:05:25|
|2|2013-08-1211:05:29|
|3|2013-08-1211:05:32|
|5|2013-08-1211:05:34|
|1|2013-08-1211:05:40|
|2|2013-08-1211:05:43|
|3|2013-08-1211:05:48|
|5|2013-08-1211:06:03|
+--------+---------------------+
8rowsinset(0.00sec)
其中userid不唯一,要求取表中每个userid对应的时间离现在最近的一条记录.初看到一个这条件一般都会想到借用临时表及添加主建借助于join操作之类的.
给一个简方法:
MySQL>selectuserid,substring_index(group_concat(atimeorderbyatimedesc),",",1)asatimefromt1groupbyuserid;
+--------+---------------------+
|userid|atime|
+--------+---------------------+
|1|2013-08-1211:05:40|
|2|2013-08-1211:05:43|
|3|2013-08-1211:05:48|
|5|2013-08-1211:06:03|
+--------+---------------------+
4rowsinset(0.03sec)
MySQL>selectuserid,substring_index(group_concat(atimeorderbyatimedesc),",",1)asatimefromt1groupbyuserid;
+--------+---------------------+
|userid|atime|
+--------+---------------------+
|1|2013-08-1211:05:40|
|2|2013-08-1211:05:43|
|3|2013-08-1211:05:48|
|5|2013-08-1211:06:03|
+--------+---------------------+
4rowsinset(0.03sec)
|