我有一个包含两个日期列的表,如何获取这两个日期之间的日期并将它们一一列出。这是测试脚本:
CREATE TABLE t1
AS
SELECT DATE '2020-1-31' AS startdate,
DATE '2020-2-3' AS enddate
FROM dual
UNION
SELECT DATE '2020-2-27' AS startdate,
DATE '2020-3-3' AS enddate
FROM dual;
SELECT *
FROM t1;
DROP TABLE t1;
我期望的结果集是:
我应该如何进行查询?提前致谢。
干得好:
SQL> select * From t1;
STARTDATE ENDDATE
---------- ----------
01/31/2020 02/03/2020
02/27/2020 03/03/2020
SQL> select a.startdate, a.enddate,
2 a.startdate + column_value - 1 dt
3 from t1 a cross join
4 table(cast(multiset(select level from dual
5 connect by level <= a.enddate - a.startdate + 1
6 ) as sys.odcinumberlist))
7 order by dt;
STARTDATE ENDDATE DT
---------- ---------- ----------
01/31/2020 02/03/2020 01/31/2020
01/31/2020 02/03/2020 02/01/2020
01/31/2020 02/03/2020 02/02/2020
01/31/2020 02/03/2020 02/03/2020
02/27/2020 03/03/2020 02/27/2020
02/27/2020 03/03/2020 02/28/2020
02/27/2020 03/03/2020 02/29/2020
02/27/2020 03/03/2020 03/01/2020
02/27/2020 03/03/2020 03/02/2020
02/27/2020 03/03/2020 03/03/2020
10 rows selected.
SQL>
这被称为行生成器技术(如果你想用谷歌搜索它的话)。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)