如何用SQL将含日期范围的行按年份拆分为多行？

我执行以下SQL查询：

SELECT ID, STARDATE, ENDDATE
FROM A
INNER JOIN B ON A.ID = B.ID
    AND B.CONTRACTID = 572786
WHERE A.ISACTIVE = 1

得到结果：

需要将结果按年份拆分，期望输出：

请问如何用SQL实现这个需求？

可以使用**递归CTE（公共表表达式）**实现日期范围按年度拆分，核心逻辑是逐行处理每条记录，将跨年度的日期拆分为单个自然年度的区间，直到覆盖整个原日期范围。

完整SQL示例 #

WITH RECURSIVE date_split AS (
    -- 初始查询：获取原始数据，计算第一条年度区间
    SELECT 
        ID,
        STARDATE,
        ENDDATE,
        EXTRACT(YEAR FROM STARDATE) AS current_year,
        STARDATE AS year_start,
        LEAST(ENDDATE, DATE_TRUNC('year', STARDATE) + INTERVAL '1 year - 1 day') AS year_end
    FROM (
        -- 嵌入原始查询逻辑
        SELECT ID, STARDATE, ENDDATE
        FROM A
        INNER JOIN B ON A.ID = B.ID
            AND B.CONTRACTID = 572786
        WHERE A.ISACTIVE = 1
    ) AS original_data

    UNION ALL

    -- 递归生成后续年度区间
    SELECT 
        ID,
        STARDATE,
        ENDDATE,
        current_year + 1 AS current_year,
        DATE_TRUNC('year', year_end) + INTERVAL '1 year' AS year_start,
        LEAST(ENDDATE, DATE_TRUNC('year', year_end) + INTERVAL '2 years - 1 day') AS year_end
    FROM date_split
    WHERE year_end < ENDDATE
)
-- 输出最终结果
SELECT 
    current_year AS YEAR,
    ID,
    year_start AS STARTDATE,
    year_end AS ENDDATE
FROM date_split
ORDER BY YEAR, ID;

逻辑拆解 #

1. 初始CTE段：先获取原始数据，同时计算每条记录对应的第一个年度区间——起始日期沿用原STARDATE，结束日期取原ENDDATE与当年12月31日的较小值，确保不超出自然年度范围。
2. 递归段：如果当前年度的结束日期未达到原记录的ENDDATE，则生成下一个年度的区间：起始日期为下一年1月1日，结束日期取原ENDDATE与下一年12月31日的较小值，循环此过程直到覆盖整个原日期范围。
3. 最终查询：从递归结果中提取目标字段，按年份和ID排序，得到符合要求的拆分结果。

方言适配提示 #

若使用MySQL等不支持DATE_TRUNC/EXTRACT的数据库，可替换为对应函数：

• 提取年份：YEAR(STARDATE) 替代 EXTRACT(YEAR FROM STARDATE)
• 获取当年1月1日：DATE_FORMAT(STARDATE, '%Y-01-01') 替代 DATE_TRUNC('year', STARDATE)
• 获取当年12月31日：DATE_FORMAT(STARDATE, '%Y-12-31') 替代 DATE_TRUNC('year', STARDATE) + INTERVAL '1 year - 1 day'

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