获取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 红包祝福语简短10字
18 周六早晨祝福语简短