(Oracle SQL)需要获取日期列的开始日期和结束日期以及每天的返回日期

Question

我有一个日期列，我需要返回从开始日期到结束日期的每一天。例如，如果我有一个从01-01-2020开始直到22/03/2022的日期列。然后我想返回数据，例如：

DateColumn
01-JAN-20
20年1月2日
20年1月3日
等等…
至2022年3月22日

来自Oracle 12:

SELECT t.id, d.day
FROM   table_name t
CROSS JOIN LATERAL (
SELECT t.start_date + LEVEL - 1 AS day
FROM   DUAL
CONNECT BY t.start_date + LEVEL - 1 <= t.end_date
) d
WHERE  t.start_date <= t.end_date

在Oracle12之前，您可以使用：

SELECT t.id, d.COLUMN_VALUE AS day
FROM   table_name t
CROSS JOIN TABLE(
CAST(
MULTISET(
SELECT t.start_date + LEVEL - 1
FROM   DUAL
CONNECT BY t.start_date + LEVEL - 1 <= t.end_date
)
AS SYS.ODCIDATELIST
)
) d
WHERE  t.start_date <= t.end_date

或者：

WITH dates (id, day, end_date) AS (
SELECT id, start_date, end_date
FROM   table_name
WHERE  start_date <= end_date
UNION ALL
SELECT id, day + 1, end_date
FROM   dates
WHERE  day + 1 <= end_date
)
SEARCH DEPTH FIRST BY id, day SET ord
SELECT id, day
FROM   dates;

对于样本数据：

CREATE TABLE table_name (id, start_date, end_date) AS
SELECT 1, DATE '2021-01-01', DATE '2022-03-22' FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-01', DATE '2022-01-10' FROM DUAL;

所有输出：

ID	DAY
1	01-JAN-21
1	21年1月2日
1	21-MAR-22
1	22-MAR-22
2	01-JAN-22
2	22年1月22日
2	22年1月9日
2	10-JAN-22

一个选项也可能是

SQL> WITH
2     test (id, start_date, end_date)
3     AS
4        (SELECT 1, DATE '2020-01-01', DATE '2022-03-22' FROM DUAL)
5      SELECT start_date + LEVEL - 1 datecolumn
6        FROM test
7  CONNECT BY LEVEL <= end_date - start_date + 1
8    ORDER BY datecolumn;
DATECOLUMN
----------
01.01.2020
02.01.2020
03.01.2020
04.01.2020
<snip>
14.03.2022
15.03.2022
16.03.2022
17.03.2022
18.03.2022
19.03.2022
20.03.2022
21.03.2022
22.03.2022
812 rows selected.
SQL>

---

如MT0所述，如果该表中包含开始/结束日期的行不止一行，则

WITH
test (id, start_date, end_date)
AS
(SELECT 1, DATE '2020-01-01', DATE '2022-03-22' FROM DUAL
UNION ALL
SELECT 2, DATE '2022-03-15', DATE '2022-03-23' FROM DUAL)
SELECT id, start_date + COLUMN_VALUE - 1 datecolumn
FROM test
CROSS JOIN
TABLE (
CAST (
MULTISET (    SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= end_date - start_date + 1)
AS SYS.odcinumberlist))
ORDER BY id, datecolumn;

您可以创建一个PIPELINED函数，只需插入您想要的日期

CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
/
CREATE OR REPLACE FUNCTION generate_dates_pipelined(
p_from IN DATE,
p_to   IN DATE
)
RETURN nt_date PIPELINED DETERMINISTIC
IS
v_start DATE := TRUNC(LEAST(p_from, p_to));
v_end   DATE := TRUNC(GREATEST(p_from, p_to));
BEGIN
LOOP
PIPE ROW (v_start);
EXIT WHEN v_start >= v_end;
v_start := v_start + INTERVAL '1' DAY;
END LOOP;
RETURN;
END generate_dates_pipelined;
/
SELECT
c.COLUMN_VALUE 
FROM   
TABLE(generate_dates_pipelined(DATE '2022-01-01',
DATE '2022-01-30')) c