Oracle SQL需求：提取两日期列间指定日期并分号拼接

原始数据与需求说明 #

现有Oracle表ABC，包含ID、From、To三列，数据示例如下：

需要为每行提取From与To日期范围内的每月1号，并将这些日期以; 分隔拼接为Result列。修正原示例中的错误后，期望输出如下：

实现方案 #

使用递归CTE生成日期范围内的所有每月1号，再通过LISTAGG函数完成字符串拼接。

完整SQL代码 #

WITH date_ranges AS (
    SELECT 
        ID,
        TO_DATE("From", 'DD/MM/YYYY') AS from_date,
        TO_DATE("To", 'DD/MM/YYYY') AS to_date
    FROM ABC
),
month_starts AS (
    SELECT 
        ID,
        CASE 
            WHEN EXTRACT(DAY FROM from_date) > 1 THEN TRUNC(ADD_MONTHS(from_date, 1), 'MM')
            ELSE TRUNC(from_date, 'MM')
        END AS month_start
    FROM date_ranges
    UNION ALL
    SELECT 
        ID,
        ADD_MONTHS(month_start, 1) AS month_start
    FROM month_starts
    JOIN date_ranges dr ON month_starts.ID = dr.ID
    WHERE ADD_MONTHS(month_start, 1) < dr.to_date
)
SELECT 
    dr.ID,
    TO_CHAR(dr.from_date, 'DD/MM/YYYY') AS "From",
    TO_CHAR(dr.to_date, 'DD/MM/YYYY') AS "To",
    LISTAGG(TO_CHAR(ms.month_start, 'DD/MM/YYYY'), '; ') WITHIN GROUP (ORDER BY ms.month_start) AS Result
FROM date_ranges dr
LEFT JOIN month_starts ms ON dr.ID = ms.ID
WHERE ms.month_start < dr.to_date
GROUP BY dr.ID, dr.from_date, dr.to_date
ORDER BY dr.ID, dr.from_date;

代码说明 #

1. date_ranges CTE：将表中字符串格式的日期转换为Oracle DATE类型，From、To为关键字，需用双引号包裹。
2. month_starts递归CTE：
   • 初始查询：生成每行第一个符合条件的每月1号——若From日期的日大于1，取下月1号；否则取当月1号。
   • 递归部分：循环生成下一个月的1号，直到日期超出To范围。
3. 主查询：通过LISTAGG将每个ID对应的所有每月1号按日期排序后拼接，最终格式化为DD/MM/YYYY字符串。

内容的提问来源于stack exchange，提问作者Jayaprakash Subramaniam