with a as(select 1 id, to_date('2008-07-12 15:20:30','yyyy-mm-dd hh24:mi:ss') time from dual union all select 2 id, to_date('2008-07-12 15:21:30','yyyy-mm-dd hh24:mi:ss') time from dual union all select 3 id, to_date('2008-07-18 15:19:30','yyyy-mm-dd hh24:mi:ss') time from dual ) select c.id, c.time from (select b.id, b.time, rownum rn from (select a.id, a.time from a where to_char(a.time, 'yyyy-mm-dd') >= to_char(sysdate - 6, 'yyyy-mm-dd') and to_char(a.time, 'yyyy-mm-dd') <= to_char(sysdate, 'yyyy-mm-dd') order by a.time) b) c where rn = (select min(rn) from (select b.id, b.time, rownum rn from (select a.id, a.time from a where to_char(a.time, 'yyyy-mm-dd') >= to_char(sysdate - 6, 'yyyy-mm-dd') and to_char(a.time, 'yyyy-mm-dd') <= to_char(sysdate, 'yyyy-mm-dd') order by a.time) b) c) or rn = (select max(rn) from (select b.id, b.time, rownum rn from (select a.id, a.time from a where to_char(a.time, 'yyyy-mm-dd') >= to_char(sysdate - 6, 'yyyy-mm-dd') and to_char(a.time, 'yyyy-mm-dd') <= to_char(sysdate, 'yyyy-mm-dd') order by a.time) b) c) 第一条是最近一周最老的纪录 第二条是最近一周最新的纪录
union all
select 2 id, to_date('2008-07-12 15:21:30','yyyy-mm-dd hh24:mi:ss') time from dual
union all
select 3 id, to_date('2008-07-18 15:19:30','yyyy-mm-dd hh24:mi:ss') time from dual
)
select c.id, c.time
from (select b.id, b.time, rownum rn
from (select a.id, a.time
from a
where to_char(a.time, 'yyyy-mm-dd') >=
to_char(sysdate - 6, 'yyyy-mm-dd')
and to_char(a.time, 'yyyy-mm-dd') <=
to_char(sysdate, 'yyyy-mm-dd')
order by a.time) b) c
where rn = (select min(rn)
from (select b.id, b.time, rownum rn
from (select a.id, a.time
from a
where to_char(a.time, 'yyyy-mm-dd') >=
to_char(sysdate - 6, 'yyyy-mm-dd')
and to_char(a.time, 'yyyy-mm-dd') <=
to_char(sysdate, 'yyyy-mm-dd')
order by a.time) b) c)
or rn = (select max(rn)
from (select b.id, b.time, rownum rn
from (select a.id, a.time
from a
where to_char(a.time, 'yyyy-mm-dd') >=
to_char(sysdate - 6, 'yyyy-mm-dd')
and to_char(a.time, 'yyyy-mm-dd') <=
to_char(sysdate, 'yyyy-mm-dd')
order by a.time) b) c)
第一条是最近一周最老的纪录
第二条是最近一周最新的纪录