如何用SQL返回两个日期之间的所有连续日期

如何用SQL返回两个日期之间的所有连续日期
-------------------------------------------


在层次查询中，Oracle引入了一个伪列level,用来表示当前行（节点）对应的level，
它从1开始计数，每多一层level的值就加1。
我们可以据此实现对两个日期/整数之间所有日期/整数的遍历。
----------------------------------------------------------
SQL> create table test (begin_date date,end_date date);

Table created

SQL> insert into test values(trunc(sysdate),trunc(sysdate+5));

1 row inserted

SQL> select * from test;

BEGIN_DATE END_DATE
----------- -----------
2010-4-6 2010-4-11

SQL> select begin_date,end_date,begin_date+level -1 as today
2 from test
3 connect by begin_date + level -1 <= end_date;

BEGIN_DATE END_DATE TODAY
----------- ----------- -----------
2010-4-6 2010-4-11 2010-4-6
2010-4-6 2010-4-11 2010-4-7
2010-4-6 2010-4-11 2010-4-8
2010-4-6 2010-4-11 2010-4-9
2010-4-6 2010-4-11 2010-4-10
2010-4-6 2010-4-11 2010-4-11

6 rows selected

------------------------------------------------------------------插入两条记录，看该查询语句是否可行
SQL> insert into test values(trunc(sysdate+4),trunc(sysdate+7));

1 row inserted

SQL> select * from test;

BEGIN_DATE END_DATE
----------- -----------
2010-4-6 2010-4-11
2010-4-10 2010-4-13

SQL> select distinct begin_date+level-1 as today
2 from test
3 connect by begin_date+level-1 <= end_date;

TODAY
-----------
2010-4-7
2010-4-13
2010-4-8
2010-4-11
2010-4-9
2010-4-6
2010-4-10
2010-4-12

8 rows selected
--------------------------------------- 根据最大和最小值得查询
SQL> delete from test where begin_date = to_date('2010-4-10','yyyy-mm-dd');

1 row deleted

SQL> select * from test;

BEGIN_DATE END_DATE
----------- -----------
2010-4-6 2010-4-11


SQL> SELECT one_date
2 FROM (SELECT start_date + level - 1 one_date
3 FROM (SELECT min(begin_date) start_date, max(end_date) end_date
4 FROM test) test
5 connect BY start_date + level - 1 <= end_date ) all_date,
6 test
7 WHERE one_date BETWEEN begin_date AND end_date;

ONE_DATE
-----------
2010-4-6
2010-4-7
2010-4-8
2010-4-9
2010-4-10
2010-4-11

6 rows selected