表结构如下:
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相同的记录中只取时间最新的一条,实现方法如下:
SELECT userid,substring_index(group_concat(atime ORDER BY atime DESC),",",1) AS atime FROM t1 GROUP BY userid;
用到了两个MySQL专有函数:
group_concat
substring_index