SQL按日期拆分记录并新增YEAR列，求替代Union的去重优化方案

嘿，这个日期区间拆分的问题用递归CTE来解决就完美了，既能避开Union带来的重复坑，还能灵活处理各种长度的日期区间，我给你详细拆解下怎么做：

首先先明确你的需求场景：

现有SQL记录结构：

ID	EFF_DT	END_DT	FLA1
FLA1	2018-01-01 00:00:00	2019-12-31 00:00:00
FLA1	2020-01-01 00:00:00	9999-12-31 00:00:00

需要按日期拆分记录，新增YEAR列，预期输出：

ID	EFF_DT	END_DT	YEAR	FLA1
FLA1	2018-01-01 00:00:00	2019-12-31 00:00:00	2019
FLA1	2020-01-01 00:00:00	2020-12-31 00:00:00	2020
FLA1	2021-01-01 00:00:00	9999-12-31 00:00:00	2021

最优方案：递归CTE（Common Table Expression） #

递归CTE是处理这类区间拆分的标准工具，它会自动遍历年份生成记录，完全不会出现Union手动拼接时的重复问题，而且能轻松处理从任意起始年到9999-12-31的“永久有效”区间。

完整SQL实现（适配你的需求） #

这里的逻辑分三部分：处理非永久有效记录、初始化永久有效记录的第一个年份、递归生成后续年份的记录：

WITH RECURSIVE split_records AS (
    -- 第一部分：处理非9999结束的记录，直接输出，YEAR取结束日期的年份
    SELECT
        ID,
        EFF_DT,
        END_DT,
        EXTRACT(YEAR FROM END_DT)::INT AS YEAR,
        FLA1
    FROM your_table_name
    WHERE END_DT != '9999-12-31 00:00:00'::TIMESTAMP
    
    UNION ALL
    
    -- 第二部分：初始化永久有效（END_DT=9999）的记录，生成第一个完整年份的区间
    SELECT
        ID,
        EFF_DT,
        CASE 
            WHEN EXTRACT(YEAR FROM EFF_DT) = EXTRACT(YEAR FROM CURRENT_DATE) 
            THEN '9999-12-31 00:00:00'::TIMESTAMP
            ELSE (EXTRACT(YEAR FROM EFF_DT) || '-12-31 00:00:00')::TIMESTAMP
        END AS END_DT,
        EXTRACT(YEAR FROM EFF_DT)::INT AS YEAR,
        FLA1
    FROM your_table_name
    WHERE END_DT = '9999-12-31 00:00:00'::TIMESTAMP
    
    UNION ALL
    
    -- 第三部分：递归生成后续年份的记录，直到当前年份，最后一条保留9999的结束日期
    SELECT
        ID,
        (YEAR + 1) || '-01-01 00:00:00'::TIMESTAMP AS EFF_DT,
        CASE 
            WHEN (YEAR + 1) = EXTRACT(YEAR FROM CURRENT_DATE) 
            THEN '9999-12-31 00:00:00'::TIMESTAMP
            ELSE (YEAR + 1) || '-12-31 00:00:00'::TIMESTAMP
        END AS END_DT,
        YEAR + 1 AS YEAR,
        FLA1
    FROM split_records
    WHERE END_DT != '9999-12-31 00:00:00'::TIMESTAMP
      AND YEAR < EXTRACT(YEAR FROM CURRENT_DATE)
)
-- 最终输出，按ID和年份排序
SELECT * FROM split_records
ORDER BY ID, YEAR;

方案优势 #

1. 无重复记录：递归逻辑严格按年份递增生成，每个年份仅生成一条记录，完全避免Union手动拼接时的重复问题；
2. 灵活可控：可以轻松调整终止年份（比如把CURRENT_DATE换成固定年份2030），适配不同业务需求；
3. 扩展性强：不管原始区间跨1年还是100年，都不需要修改代码，递归会自动处理；

注意事项 #

• 确保你的数据库支持递归CTE（MySQL 8.0+、PostgreSQL、SQL Server等主流数据库都支持）；
• 确认日期字段的类型是TIMESTAMP或DATE，避免类型转换错误；
• 如果“永久有效”的定义不是到当前年份，而是一直到9999，可以去掉AND YEAR < EXTRACT(YEAR FROM CURRENT_DATE)的条件，但这样会生成到9999年的所有年份记录，可能会有性能问题，建议根据实际业务设置合理的终止年份。

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