获取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!
热门推荐
10 八一幼儿祝福语大全简短
11 公司乔迁食堂祝福语简短
12 婚礼结束聚餐祝福语简短
13 儿媳买车妈妈祝福语简短
14 毕业送礼老师祝福语简短
15 同事辞职正常祝福语简短
16 恭贺新婚文案祝福语简短
17 金店立秋祝福语简短英文
18 婆婆高寿祝福语大全简短