MSSQL15中按日期拆分时间区间的SQL查询需求

原始数据 #

预期拆分结果 #

遇到的问题 #

• 部分拆分方案仅能生成首尾两天的拆分结果，无法覆盖跨多日区间的中间完整日期
• 数据库为SQL Server 15，不支持generate_series()函数
• 不清楚如何将单日期拆分逻辑适配到整张表的批量处理

解决方案：递归CTE实现批量按日拆分 #

以下查询可实现整张表的时间区间按日拆分，自动生成中间日期的完整分段记录：

WITH DateSplitter AS (
    -- 锚点成员：初始化每条原始记录的首段拆分区间
    SELECT 
        Start AS SegmentStart,
        CASE 
            WHEN CAST(Start AS DATE) = CAST(End AS DATE) THEN End
            ELSE DATEADD(MILLISECOND, -3, DATEADD(DAY, 1, CAST(Start AS DATE)))
        END AS SegmentEnd,
        End AS OriginalEnd,
        Start AS OriginalStart
    FROM YourTableName  -- 替换为你的实际表名
    UNION ALL
    -- 递归成员：逐天生成后续分段区间
    SELECT 
        DATEADD(DAY, 1, CAST(SegmentStart AS DATE)) AS SegmentStart,
        CASE 
            WHEN DATEADD(DAY, 1, CAST(SegmentStart AS DATE)) = CAST(OriginalEnd AS DATE) THEN OriginalEnd
            ELSE DATEADD(MILLISECOND, -3, DATEADD(DAY, 2, CAST(SegmentStart AS DATE)))
        END AS SegmentEnd,
        OriginalEnd,
        OriginalStart
    FROM DateSplitter
    WHERE DATEADD(DAY, 1, CAST(SegmentStart AS DATE)) <= CAST(OriginalEnd AS DATE)
)
SELECT 
    SegmentStart AS Start,
    SegmentEnd AS End
FROM DateSplitter
ORDER BY Start;

逻辑说明 #

1. 锚点成员：处理每条原始记录的第一天区间，若起止时间在同一天则直接保留，否则拆分为起始时间到当日23:59:59.997（适配SQL Server datetime类型3毫秒精度限制）
2. 递归成员：从首段区间的次日开始，逐天生成完整日期区间，直到覆盖原始区间的结束日期，最后一天直接使用原始结束时间
3. 最终结果按拆分后的起始时间排序，保证记录顺序正确

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