如何扩展日期范围并生成时段数据？SQL实现求助

原查询的问题点 #

• 递归CTE引用错误：递归部分调用了不存在的DateGenerate表，应引用CTE本身Daterange
• 日期转换格式错误：用114格式转换纯日期字符串会导致解析失败，需改用112（ISO无分隔符日期格式）
• 硬编码字段值不符合需求：输出的起止时间、时段、时长未匹配实际业务逻辑
• 不必要的UNION ALL：与Slottable的合并逻辑和需求无关

修正后的SQL代码 #

WITH Daterange AS (
    -- 初始化日期范围的起始日期
    SELECT CONVERT(datetime, '20240101', 112) AS MyDate
    UNION ALL
    -- 递归生成后续日期，直到结束日期
    SELECT DATEADD(DAY, 1, MyDate)
    FROM Daterange
    WHERE MyDate <= CONVERT(datetime, '20240105', 112)
)
SELECT
    -- 输出YYYYMMDD格式的日期字符串，匹配原始数据格式
    CONVERT(varchar(8), MyDate, 112) AS 日期,
    8000000 AS 开始时间,
    17000000 AS 结束时间,
    30 AS 时段,
    9 AS 当日时长
FROM Daterange

代码说明 #

1. 递归日期生成：通过CTE正确生成20240101至20240105的所有日期，用112格式确保纯日期字符串被正确解析
2. 字段精准匹配：直接输出需求指定的开始时间、结束时间、时段和当日时长，完全贴合业务要求
3. 可选时段明细扩展：如果需要拆分出每日的30分钟时段明细（而非仅每日汇总），可以使用以下扩展逻辑：

WITH Daterange AS (
    SELECT CONVERT(datetime, '20240101', 112) AS MyDate
    UNION ALL
    SELECT DATEADD(DAY, 1, MyDate)
    FROM Daterange
    WHERE MyDate <= CONVERT(datetime, '20240105', 112)
),
TimeSlots AS (
    -- 生成08:00到17:00之间的所有30分钟时段
    SELECT TOP (DATEDIFF(MINUTE, '08:00', '17:00') / 30)
        DATEADD(MINUTE, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) * 30, '08:00') AS SlotStart,
        DATEADD(MINUTE, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) * 30, '08:00') AS SlotEnd
    FROM sys.all_columns
)
SELECT
    CONVERT(varchar(8), d.MyDate, 112) AS 日期,
    -- 将时段转换为需求的数字格式
    CAST(REPLACE(CONVERT(varchar(8), t.SlotStart, 108), ':', '') AS bigint) AS 时段开始,
    CAST(REPLACE(CONVERT(varchar(8), t.SlotEnd, 108), ':', '') AS bigint) AS 时段结束,
    30 AS 时段长度,
    9 AS 当日总时长
FROM Daterange d
CROSS JOIN TimeSlots t
ORDER BY 日期, 时段开始

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