Oracle SQL按ID分组拼接From与To区间内每月首日

需求说明 #

按ID分组，将每组内每条记录的From到To日期区间内的所有月份首日，格式化为DD/MM/YYYY格式后用; （分号加空格）分隔拼接，最终输出到Result列。

原表结构及数据（表名：ABC） #

期望输出结果 #

实现方案 #

使用递归CTE生成每个日期区间内的所有月份首日，再通过LISTAGG函数按ID分组拼接结果：

WITH date_ranges AS (
    SELECT 
        ID,
        -- 将字符串日期转为DATE类型，并取当月首日
        TRUNC(TO_DATE("From", 'DD/MM/YYYY'), 'MM') AS month_start,
        TRUNC(TO_DATE("To", 'DD/MM/YYYY'), 'MM') AS month_end
    FROM ABC
),
recursive_months AS (
    -- 初始化：取每个区间的起始月份
    SELECT ID, month_start AS current_month
    FROM date_ranges
    UNION ALL
    -- 递归生成后续月份，直到超过区间结束月份
    SELECT rm.ID, ADD_MONTHS(rm.current_month, 1)
    FROM recursive_months rm
    JOIN date_ranges dr ON dr.ID = rm.ID
    WHERE ADD_MONTHS(rm.current_month, 1) <= dr.month_end
)
SELECT 
    ID,
    -- 按日期顺序拼接格式化后的月份首日
    LISTAGG(TO_CHAR(current_month, 'DD/MM/YYYY'), '; ') WITHIN GROUP (ORDER BY current_month) AS Result
FROM recursive_months
GROUP BY ID;

关键逻辑说明 #

1. date_ranges CTE：处理原表的字符串日期，转换为DATE类型并截取当月首日，得到每个记录的月份区间范围。
2. recursive_months CTE：通过递归方式，从每个区间的起始月份开始，逐月生成直到区间结束月份的所有月份首日。
3. LISTAGG拼接：按ID分组，将所有生成的月份首日格式化为指定字符串后，用; 分隔拼接，同时按日期排序保证结果顺序。

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