我的问题是这样的,我的表中有若干条记录,每条记录有时间,我想将将逾期5天的记录筛选出来(距离今天)
记录中有重复的,事例如下
100 2011-10-1
100 2011-10-24
100 2011-10-27
200 2011-10-28
200 2011-10-29
300 2011-10-15
300 2011-10-30想直接删选出
100 2011-10-1
100 2011-10-24
100 2011-10-27
200 2011-10-28
200 2011-10-29
300 2011-10-15
300 2011-10-30
红色标记的
记录中有重复的,事例如下
100 2011-10-1
100 2011-10-24
100 2011-10-27
200 2011-10-28
200 2011-10-29
300 2011-10-15
300 2011-10-30想直接删选出
100 2011-10-1
100 2011-10-24
100 2011-10-27
200 2011-10-28
200 2011-10-29
300 2011-10-15
300 2011-10-30
红色标记的
with a as
(select '100' as id, to_date('20111001', 'yyyymmdd') as sdate from dual union
select '100' as id, to_date('20111024', 'yyyymmdd') as sdate from dual union
select '100' as id, to_date('20111027', 'yyyymmdd') as sdate from dual union
select '200' as id, to_date('20111028', 'yyyymmdd') as sdate from dual union
select '200' as id, to_date('20111029', 'yyyymmdd') as sdate from dual union
select '300' as id, to_date('20111015', 'yyyymmdd') as sdate from dual union
select '300' as id, to_date('20111030', 'yyyymmdd') as sdate from dual
)
select * from a where sdate >= to_char(trunc(sysdate) - 5)
select * from t1 where sheet_no not in (select sheet_no
from (select sheet_no,row_number() over(partition by sheet_no order by create_date desc) as isort
from t1 where (sysdate-create_date)*24*60*60 <'500000') t
where t.isort = 1);