本帖最后由 suphy23 于 2012-10-26 10:42 编辑
大概看懂他的意思了,不过我觉得他写的有笔误,我翻译一下
有这样一张表:
SQL> with t_time as
2 (select 1 id,'0030' timeflag from dual
3 union all
4 select 2 id,'0100' timeflag from dual
5 union all
6 select 3 id,'0200' timeflag from dual
7 union all
8 select 4 id,'1000' timeflag from dual
9 union all
10 select 5 id,'2000' timeflag from dual
11 union all
12 select 6 id,'2300' timeflag from dual)
13 select * from t_time;
ID TIMEFLAG --TIMEFLAG表示时分(HH24:MI)
---------- --------
1 0030
2 0100
3 0200
4 1000
5 2000
6 2300
6 rows selected
需求:
以TIMEFLAG升序排列,将该表的时间字段与当前时间做比较,取比当前时间大的后两条记录。
比如:
若当前时间为09:00(上午9点钟),那么就取id为4、5的两条记录;
若当前时间为22:00(晚上10点钟),那么就取id为6、1的两条记录;
若当前时间为23:30(晚上11点半),那么就取id为1、2的两条记录;
解答:
with t_time as
(select 1 id,'0030' timeflag from dual
union all
select 2 id,'0100' timeflag from dual
union all
select 3 id,'0200' timeflag from dual
union all
select 4 id,'1000' timeflag from dual
union all
select 5 id,'2000' timeflag from dual
union all
select 6 id,'2300' timeflag from dual)
select id,decode(sign(timeflag-'2400'),1,to_char(timeflag-'2400'),timeflag) timeflag
from (select id,timeflag,rownum rn
from (select id,timeflag from t_time
union all
select id,to_char(timeflag+'2400') timeflag from t_time where rownum<3) t1
where timeflag>to_char(sysdate,'hh24:mi')
order by timeflag)
where rn <=2;