获取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!
热门推荐
1 训练狗狗基础
2 怎么训练狗狗笑
3 怎么训练狗狗卧下
4 上海狗狗训练基地
5 训练狗狗排便
6 狗狗基础训练
8 如何训练狗狗装死
9 狗狗站姿训练
10 狗狗训练用品
11 训练狗狗吧
12 狗狗要怎么训练
13 怎么训练狗狗玩球
14 训练狗狗唤回
15 狗狗训练躺下
16 狗狗训练指令
17 狗狗的训练技巧
18 狗狗不好训练