要实现的效果如下:
WORKING_DATE NEXT_WORKING_DATE
---------------------------------------
2010-5-1 2010-5-2
2010-5-2 2010-5-3
2010-5-3 2010-5-4
2010-5-4 2010-5-6
2010-5-6 2010-5-8
2010-5-8 2010-5-30
2010-5-30 create table bank_working_date
(
working_date date
)insert into bank_working_date values(to_date('2010-05-01', 'yyyy-mm-dd'))
insert into bank_working_date values(to_date('2010-05-02', 'yyyy-mm-dd'));
insert into bank_working_date values(to_date('2010-05-03', 'yyyy-mm-dd'));
insert into bank_working_date values(to_date('2010-05-04', 'yyyy-mm-dd'));
insert into bank_working_date values(to_date('2010-05-06', 'yyyy-mm-dd'));
insert into bank_working_date values(to_date('2010-05-08', 'yyyy-mm-dd'));
insert into bank_working_date values(to_date('2010-05-30', 'yyyy-mm-dd'));
-- 我本人给出的SQL
select a.working_date, min(b.working_date) next_working_date
from bank_working_date a left join bank_working_date b
on b.working_date > a.working_date
group by a.working_date
请教其它写法.谢谢
WORKING_DATE NEXT_WORKING_DATE
---------------------------------------
2010-5-1 2010-5-2
2010-5-2 2010-5-3
2010-5-3 2010-5-4
2010-5-4 2010-5-6
2010-5-6 2010-5-8
2010-5-8 2010-5-30
2010-5-30 create table bank_working_date
(
working_date date
)insert into bank_working_date values(to_date('2010-05-01', 'yyyy-mm-dd'))
insert into bank_working_date values(to_date('2010-05-02', 'yyyy-mm-dd'));
insert into bank_working_date values(to_date('2010-05-03', 'yyyy-mm-dd'));
insert into bank_working_date values(to_date('2010-05-04', 'yyyy-mm-dd'));
insert into bank_working_date values(to_date('2010-05-06', 'yyyy-mm-dd'));
insert into bank_working_date values(to_date('2010-05-08', 'yyyy-mm-dd'));
insert into bank_working_date values(to_date('2010-05-30', 'yyyy-mm-dd'));
-- 我本人给出的SQL
select a.working_date, min(b.working_date) next_working_date
from bank_working_date a left join bank_working_date b
on b.working_date > a.working_date
group by a.working_date
请教其它写法.谢谢
from bank_working_date;
WORKING_DATE
------------
2010-5-1
2010-5-2
2010-5-3
2010-5-4
2010-5-6
2010-5-8
2010-5-30
7 rows selected
SQL>
SQL> select working_date,lead(working_date) over(order by working_date)as next_working_date
2 from bank_working_date;
WORKING_DATE NEXT_WORKING_DATE
------------ -----------------
2010-5-1 2010-5-2
2010-5-2 2010-5-3
2010-5-3 2010-5-4
2010-5-4 2010-5-6
2010-5-6 2010-5-8
2010-5-8 2010-5-30
2010-5-30
7 rows selected