4班组8周循环昼夜排班查询表构建SQL方案求助

问题核心 #

原代码存在两个关键问题：

• 错误使用9天循环（(Days+1) %9），实际排班是8天循环（2天×4组）
• 按小时生成时间点，不符合「按分钟输出」的需求

修正后的完整代码 #

;WITH samples as (
    -- 从起始时间开始，按分钟生成时间序列
    SELECT CAST('2023-04-01 09:00' AS DATETIME) AS Sample
    UNION ALL
    SELECT DATEADD(minute, 1, Sample)
    FROM Samples
    WHERE Sample <= '2026-01-01'
), ExtendedSamples as (
    -- 计算从起始班次开始的天数差（起始日Days=0）
    SELECT 
        Sample,
        DATEDIFF(hour, '2023-04-01 09:00', Sample) / 24 AS Days
    FROM Samples 
), Shifts as (
    -- 按8天循环逻辑匹配排班
    SELECT *,
        CASE 
            WHEN Days % 8 IN (0,1) THEN 'B/G'  -- 第1-2天：白班Blue/夜班Green
            WHEN Days % 8 IN (2,3) THEN 'W/B'  -- 第3-4天：白班White/夜班Blue
            WHEN Days % 8 IN (4,5) THEN 'R/W'  -- 第5-6天：白班Red/夜班White
            WHEN Days % 8 IN (6,7) THEN 'G/R'  -- 第7-8天：白班Green/夜班Red
        END AS Shifts
    FROM ExtendedSamples 
) 
SELECT 
    Sample,
    Days,
    DATENAME(WEEKDAY, Sample) AS WeekDay,
    Shifts,
    -- 准确匹配班次时间：白班09:00-18:00（不含18:00），夜班18:00-次日09:00
    CASE 
        WHEN DATEPART(hour, Sample) >=9 AND DATEPART(hour, Sample) <18 
            THEN SUBSTRING(Shifts, 1, 1) 
        ELSE SUBSTRING(Shifts, 3, 1) 
    END AS Wholetime_Shift
FROM Shifts
OPTION (MAXRECURSION 0);

关键改动说明 #

1. 时间序列精度调整：将DATEADD(hour, 1, Sample)改为DATEADD(minute, 1, Sample)，生成每分钟的时间点
2. 循环周期修正：将原代码的(Days+1) %9改为Days %8，对应8天循环的排班规则
3. 班次时间判断优化：把DATEPART(hour, Sample) between 9 and 17调整为DATEPART(hour, Sample) >=9 AND DATEPART(hour, Sample) <18，确保18:00整正确归属夜班
4. 排班逻辑对齐：根据8天循环的顺序，调整CASE条件对应的天数区间，完全匹配需求中的排班顺序

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