select * from table1 t where not exist( select 1 from table1 where table1.userid = t.user and table1.createdate > t.createdate and createdate < TO_DATE('2013-8-12','YYYY/MM/DD')) and createdate < TO_DATE('2013-8-12','YYYY/MM/DD') 我目前是这样写的,部知道其他方法该怎么写
select t1.id,t1.userid,t1.createdate from table1 t1, (select userid, max(createdate) as createdate from table1 where createdate<to_date('2013-8-12','yyyy-mm-dd') group by userid) t2 where t1.userid=t2.userid and t1.createdate=t2.createdate order by t1.id; 结果:
2、row_number() over()排序取每组第一行数据
3、having count() (少用)
4、比较 子查询
and createdate < TO_DATE('2013-8-12','YYYY/MM/DD')
我目前是这样写的,部知道其他方法该怎么写
噢,开始没仔细看。。那你这需求 挺简单的。分析函数、、排序+rownum 、子查询 等等
(select userid, max(createdate) as createdate
from table1
where createdate<to_date('2013-8-12','yyyy-mm-dd')
group by userid) t2
where t1.userid=t2.userid and t1.createdate=t2.createdate
order by t1.id;
结果: