在层次查询中，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
