时间戳

首页 > 解决方案 > 甲骨文 SQL。获取两个日期之间的日期

sql - 甲骨文 SQL。获取两个日期之间的日期

问题描述

我有一个包含两个日期列的表格，如何获取这两个日期之间的日期并一一列出。这是测试脚本：

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;

我期待的结果集是：

在此处输入图像描述

我应该如何进行查询？提前致谢。

标签： sqloracle

解决方案

对于实际的 Oracle 版本：

SELECT *
FROM t1,
     lateral(
           select level N,
                  t1.startdate+level-1 dt
           from dual
           connect by level <= t1.enddate - t1.startdate + 1
     );

或者

SELECT 
  startdate,
  enddate,
  startdate+n as DT
FROM t1,
     xmltable('0 to xs:integer(.)' 
              passing cast(enddate-startdate as number)
               columns n int path '.'
             )

完整示例：

with 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,
     lateral(
           select level N,
                  t1.startdate+level-1 dt
           from dual
           connect by level <= t1.enddate - t1.startdate + 1
     );

with 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 
  startdate,
  enddate,
  startdate+n as DT
FROM t1,
     xmltable('0 to xs:integer(.)' 
              passing cast(enddate-startdate as number)
               columns n int path '.'
             );

推荐阅读