group by 后取最新的纪录问题Table:item_record
+--------+----------+--------+--------+---------------------+
| userid | num | tid | itemid | record_date |
+--------+----------+--------+--------+---------------------+
| 341 | 1 | 2 | 92 | 2008-01-28 18:39:59 |
| 341 | 1 | 2 | 92 | 2008-01-28 18:40:39 |
| 341 | 1 | 1 | 92 | 2008-01-28 18:40:52 |
| 341 | 1 | 2 | 92 | 2008-01-28 18:50:59 |
| 341 | 2 | 1 | 92 | 2008-01-28 18:34:31 |
| 341 | 3 | 2 | 92 | 2008-01-28 18:34:04 |
| 341 | 5 | 1 | 92 | 2008-01-28 18:26:56 |
| 341 | 5 | 2 | 92 | 2008-01-28 18:34:59 |
| 341 | 10 | 2 | 92 | 2008-01-28 18:26:10 |
+--------+----------+--------+--------+---------------------+
select * from item_record where tid=2 and itemid=92 group by userid order by record_date desc这句会select了num大的纪录(10)...请问怎样group by userid 后, 是取得最近日期的纪录(2008-01-28 18:50:59)
取出这个纪录
| 341 | 1 | 2 | 92 | 2008-01-28 18:50:59 |谢谢
+--------+----------+--------+--------+---------------------+
| userid | num | tid | itemid | record_date |
+--------+----------+--------+--------+---------------------+
| 341 | 1 | 2 | 92 | 2008-01-28 18:39:59 |
| 341 | 1 | 2 | 92 | 2008-01-28 18:40:39 |
| 341 | 1 | 1 | 92 | 2008-01-28 18:40:52 |
| 341 | 1 | 2 | 92 | 2008-01-28 18:50:59 |
| 341 | 2 | 1 | 92 | 2008-01-28 18:34:31 |
| 341 | 3 | 2 | 92 | 2008-01-28 18:34:04 |
| 341 | 5 | 1 | 92 | 2008-01-28 18:26:56 |
| 341 | 5 | 2 | 92 | 2008-01-28 18:34:59 |
| 341 | 10 | 2 | 92 | 2008-01-28 18:26:10 |
+--------+----------+--------+--------+---------------------+
select * from item_record where tid=2 and itemid=92 group by userid order by record_date desc这句会select了num大的纪录(10)...请问怎样group by userid 后, 是取得最近日期的纪录(2008-01-28 18:50:59)
取出这个纪录
| 341 | 1 | 2 | 92 | 2008-01-28 18:50:59 |谢谢
(select userid,max(record_date) as ma from tt group by userid) b
on a.userid=b.userid and a.record_date=b.ma
要分组并显示排列分组内容,可以通过order by两列实现。
因为有其他userid,要group啊,不然取出tid=2,itemid=92时会有很多相同userid的纪录目的是取出tid=2,itemid=92,有什么人有纪录,而又取出最新的num
http://blog.chinaunix.net/u/29134/showart_411484.html