如何用SQL返回两个日期之间的所有连续日期
Kaya 发表于 os2ora.com
举个例子，假设有两个日期 2009-03-20和2009-03-23，如果用SQL返回2009-03-20, 2009-03-21, 2009-03-22, 2009-03-23。
更一般的，当一个表里面有两个列，分别为begin_date和end_date时，如何针对这个表的每一行，用SQL返回begin_date和end_date之间的所有连续日期。
在层次查询中，Oracle引入了一个伪列level,用来表示当前行（节点）对应的level，它从1开始计数，每多一层level的值就加1。我们可以据此实现对两个日期/整数之间所有日期/整数的遍历。
SQL> CREATE TABLE test (begin_date date,end_date date); SQL> INSERT INTO test VALUES(trunc(sysdate), trunc(sysdate+5)); SQL> SELECT begin_date, end_date, begin_date + level -1 day FROM test connect BY begin_date + level -1 <= end_date; BEGIN_DAT END_DATE DAY --------- --------- --------- 23-MAR-09 28-MAR-09 23-MAR-09 23-MAR-09 28-MAR-09 24-MAR-09 23-MAR-09 28-MAR-09 25-MAR-09 23-MAR-09 28-MAR-09 26-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 6 rows selected.
当test表里面有多行时，这个方法还行得通吗？
SQL> INSERT INTO test VALUES(trunc(sysdate+4),trunc(sysdate+7)); SQL> SELECT begin_date, end_date, begin_date + level -1 day FROM test connect BY begin_date + level -1 <= end_date ORDER BY 1,2,3; BEGIN_DAT END_DATE DAY --------- --------- --------- 23-MAR-09 28-MAR-09 23-MAR-09 23-MAR-09 28-MAR-09 24-MAR-09 23-MAR-09 28-MAR-09 24-MAR-09 23-MAR-09 28-MAR-09 25-MAR-09 23-MAR-09 28-MAR-09 25-MAR-09 23-MAR-09 28-MAR-09 25-MAR-09 23-MAR-09 28-MAR-09 25-MAR-09 23-MAR-09 28-MAR-09 26-MAR-09 23-MAR-09 28-MAR-09 26-MAR-09 23-MAR-09 28-MAR-09 26-MAR-09 23-MAR-09 28-MAR-09 26-MAR-09 23-MAR-09 28-MAR-09 26-MAR-09 23-MAR-09 28-MAR-09 26-MAR-09 23-MAR-09 28-MAR-09 26-MAR-09 23-MAR-09 28-MAR-09 26-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 27-MAR-09 30-MAR-09 27-MAR-09 27-MAR-09 30-MAR-09 28-MAR-09 27-MAR-09 30-MAR-09 28-MAR-09 27-MAR-09 30-MAR-09 29-MAR-09 27-MAR-09 30-MAR-09 29-MAR-09 27-MAR-09 30-MAR-09 29-MAR-09 27-MAR-09 30-MAR-09 29-MAR-09 27-MAR-09 30-MAR-09 30-MAR-09 27-MAR-09 30-MAR-09 30-MAR-09 27-MAR-09 30-MAR-09 30-MAR-09 27-MAR-09 30-MAR-09 30-MAR-09 27-MAR-09 30-MAR-09 30-MAR-09 27-MAR-09 30-MAR-09 30-MAR-09 27-MAR-09 30-MAR-09 30-MAR-09 27-MAR-09 30-MAR-09 30-MAR-09 62 rows selected.
或许应该加上一个distinct
SQL> SELECT DISTINCT begin_date, end_date, begin_date + level -1 day FROM test connect BY begin_date + level -1 <= end_date ORDER BY 1,2,3; BEGIN_DAT END_DATE DAY --------- --------- --------- 23-MAR-09 28-MAR-09 23-MAR-09 23-MAR-09 28-MAR-09 24-MAR-09 23-MAR-09 28-MAR-09 25-MAR-09 23-MAR-09 28-MAR-09 26-MAR-09 23-MAR-09 28-MAR-09 27-MAR-09 23-MAR-09 28-MAR-09 28-MAR-09 27-MAR-09 30-MAR-09 27-MAR-09 27-MAR-09 30-MAR-09 28-MAR-09 27-MAR-09 30-MAR-09 29-MAR-09 27-MAR-09 30-MAR-09 30-MAR-09 10 rows selected. SQL>
另外一种方法是先得到min(begin_date) 和 max(end_date)之间所有的连续日期，假设这些日期放在表all_date里面，再用表all_date和原来的表test做连接，从而得到最后的结果。
SELECT one_date FROM (SELECT start_date + level - 1 one_date FROM (SELECT min(start_date) start_date, max(end_date) end_date FROM test) test connect BY start_date + level - 1 < = end_date ) all_date, test WHERE one_date BETWEEN start_date AND end_date;
下篇文章探讨下两种方式的性能。
你好，请问为什么
SELECT to_char((to_date(’20090202′, ‘yyyyMMdd’) + level – 1), ‘yyyyMMdd’) day
FROM dual
connect BY to_date(’20090202′, ‘yyyyMMdd’) + level – 1 <=
to_date('20090102', 'yyyyMMdd');
会返回一条记录呢?应该是一条都不返回啊
还有一个问题，connect BY不能超过100层啊，我用的是92
[...] 最后从http://www.os2ora.com/how-to-return-continuous-dates-between-two-dates/发现一个同样的案例,OS2提供了一个非常不错的方法（关于connect by的性能分析详见http://www.os2ora.com/connect-by-performance-tunning/）： [...]