如下表：
|
1
2
3
4
5
6
7
8
9
10
11
|
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； |
数据如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
+--------+---------------------+| 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操作之类的．
给一个简方法：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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!
