编写SQL Query实现跨天起止时间按日拆分为多行的问题求助

原查询的核心问题是递归阶段错误地沿用了带具体时间的起始值，导致中间日期的起始时间不符合预期。以下是修正后的实现：

WITH date_range_cte AS (
    -- 初始查询：获取原始记录及对应的起止日期
    SELECT 
        CLOCKING_ID,
        CAST(START_DATE_TIME AS DATETIME) AS ORIG_START,
        CAST(END_DATE_TIME AS DATETIME) AS ORIG_END,
        CAST(START_DATE_TIME AS DATE) AS CURRENT_DATE
    FROM [time_tbl]
    WHERE ID = 18 -- 可替换为目标ID，或去掉此条件处理全表
        AND END_DATE_TIME IS NOT NULL
        AND START_DATE_TIME IS NOT NULL
    UNION ALL
    -- 递归生成中间日期
    SELECT 
        CLOCKING_ID,
        ORIG_START,
        ORIG_END,
        DATEADD(DAY, 1, CURRENT_DATE) AS CURRENT_DATE
    FROM date_range_cte
    WHERE DATEADD(DAY, 1, CURRENT_DATE) <= CAST(ORIG_END AS DATE)
)
-- 计算每天的实际起止时间
SELECT 
    CLOCKING_ID,
    -- 确定当日起始时间：首日用原始开始时间，其他日期用当日0点
    CASE
        WHEN CURRENT_DATE = CAST(ORIG_START AS DATE) THEN ORIG_START
        ELSE CAST(CURRENT_DATE AS DATETIME)
    END AS START_DATE_TIME,
    -- 确定当日结束时间：末日用原始结束时间，其他日期用当日23:59:59.997（适配SQL Server datetime精度）
    CASE
        WHEN CURRENT_DATE = CAST(ORIG_END AS DATE) THEN ORIG_END
        ELSE DATEADD(MILLISECOND, -3, CAST(DATEADD(DAY, 1, CURRENT_DATE) AS DATETIME))
    END AS END_DATE_TIME
FROM date_range_cte
ORDER BY CURRENT_DATE
OPTION (MAXRECURSION 0);

关键修正点说明： #

• 递归逻辑调整：递归阶段仅生成纯日期序列（CURRENT_DATE），不再沿用带时间的起始值，确保每个日期都是完整的自然日。
• 起止时间计算优化：
  • 首日直接使用原始记录的ORIG_START，其他日期自动转换为当日0点的datetime。
  • 末日直接使用原始记录的ORIG_END，其他日期通过DATEADD(MILLISECOND, -3, ...)获取当日23:59:59.997（适配SQL Server datetime类型3毫秒的精度限制，避免数据遗漏）。
• 逻辑简化：通过CURRENT_DATE与原始起止日期的直接对比，清晰区分首日、中间日和末日，去掉冗余判断条件。

测试验证： #

对于示例中的跨天记录（2022-08-17 14:22:56.277 至 2022-08-22 15:02:41.523），修正后的查询会生成6行符合预期的结果：

• 2022-08-17：14:22:56.277 至 23:59:59.997
• 2022-08-18：00:00:00.000 至 23:59:59.997
• 2022-08-19：00:00:00.000 至 23:59:59.997
• 2022-08-20：00:00:00.000 至 23:59:59.997
• 2022-08-21：00:00:00.000 至 23:59:59.997
• 2022-08-22：00:00:00.000 至 15:02:41.523

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