数据如下
Table logid user_id weights create_time
1 1 402 2014-04-02 09:10:40
2 5 1012 2014-04-02 15:23:52
3 2 402 2014-04-08 08:57:32
4 1 402 2014-04-08 09:53:39
想要得到的查询效果为:2 5 1012 2014-04-02 15:23:52
4 1 402 2014-04-08 09:53:39
请问sql语句如何写
Table logid user_id weights create_time
1 1 402 2014-04-02 09:10:40
2 5 1012 2014-04-02 15:23:52
3 2 402 2014-04-08 08:57:32
4 1 402 2014-04-08 09:53:39
想要得到的查询效果为:2 5 1012 2014-04-02 15:23:52
4 1 402 2014-04-08 09:53:39
请问sql语句如何写
or
date(create_time)=date(create_time) and a.weights=weights and a.id<id
)
Table log user_id weights create_time
1 402 2014-04-02 09:10:40
5 1012 2014-04-02 15:23:52
2 402 2014-04-08 08:57:32
1 402 2014-04-08 09:53:39
4 402 2014-04-08 10:53:39
1 402 2014-04-08 11:53:39
3 5002 2014-04-11 11:53:39
想要得到的查询效果为:5 1012 2014-04-02 15:23:52
1 402 2014-04-08 11:53:39
3 5002 2014-04-11 11:53:39
select userid,max(create_time) max_create_time from log group by userid) tmp,log
where tmp.userid = log.userid and tmp.max_create_time = log.create_time order by log.create_time desc
-- 大概如下,日期函数,我也不大明白,按日期取最大的ID,再取这些ID的行就可以了。
select * from mytable where id in (select max(id) from mytable group by date_format(create_time,'%Y-%m-%d'))
from log t
where not exists (select 1 from log where date(create_time)=date(t.create_time) and weights>t.weights)
[征集]分组取最大N条记录方法征集,及散分....
or
date(create_time)=date(create_time) and a.weights=weights and a.create_time<create_time
)