获取MySQL的表中每个userid最后一条记录的方法
如下表：
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)
Goodluck!