获取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 夜雨祝福语简短10字
16 蛋糕祝福语简短闺蜜
17 部门过年祝福语简短独特
18 简短精炼的结婚祝福语