如何编写SQL查询生成指定每周每日5个调度时段的排班表（含空时段）

要实现展示指定周内每日全部5个调度时段（无论是否分配任务），核心思路是先构建所有日期与5个时段的完整组合，再左连接Jobs表匹配已分配任务，以此保留所有时段条目，无任务的自然显示为Empty。

完整SQL查询（以SQL Server为例） #

-- 生成固定的5个调度时段
WITH Slots AS (
    SELECT 1 AS SlotNum UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5
),
-- 生成指定周的日期范围（2024-03-24至2024-03-30）
DateRange AS (
    SELECT CAST('2024-03-24' AS DATE) AS ScheduleDate
    UNION ALL
    SELECT DATEADD(DAY, 1, ScheduleDate)
    FROM DateRange
    WHERE ScheduleDate < '2024-03-30'
),
-- 预处理Jobs表，给每个任务分配对应时段序号（支持同时段多任务）
JobSlots AS (
    SELECT 
        JobNum,
        CAST(StartDate AS DATE) AS JobDate,
        Line,
        -- 用DENSE_RANK确保同时段多任务共享同一Slot编号
        DENSE_RANK() OVER (PARTITION BY CAST(StartDate AS DATE), Line ORDER BY StartDate) AS SlotNum
    FROM Jobs
    WHERE StartDate BETWEEN '2024-03-24' AND '2024-03-30'
      AND Line = 'Line A'
)
-- 交叉连接日期与时段，左连接任务数据生成完整排班
SELECT
    DATENAME(WEEKDAY, dr.ScheduleDate) AS WeekDayName,
    FORMAT(dr.ScheduleDate, 'MM/dd') AS FormattedDate,
    s.SlotNum AS Slot,
    CASE WHEN js.JobNum IS NOT NULL THEN 'Job# ' + js.JobNum ELSE 'Empty' END AS JobStatus
FROM DateRange dr
CROSS JOIN Slots s
LEFT JOIN JobSlots js 
    ON dr.ScheduleDate = js.JobDate
    AND s.SlotNum = js.SlotNum
    AND js.Line = 'Line A'
ORDER BY dr.ScheduleDate, s.SlotNum;

关键细节说明 #

• Slots CTE：生成固定的5个时段条目，保证每个日期都能覆盖全部调度时段。
• DateRange CTE：通过递归方式生成指定起止日期的所有日期，替换起止日期即可适配其他周。
• JobSlots CTE：使用DENSE_RANK()替代原查询的ROW_NUMBER()，确保同一时段的多个任务会被分配相同的Slot编号（匹配示例中周二03时段存在两个Job的场景）。
• 左连接+交叉连接：确保所有日期-时段组合都被保留，无匹配任务的条目自动显示Empty。

预期输出示例 #

周一 - 03/25 - 01 - Job# 123456
周一 - 03/25 - 02 - Job# 123457  
周一 - 03/25 - 03 - Job# 123458
周一 - 03/25 - 04 - Empty
周一 - 03/25 - 05 - Empty
周二 - 03/26 - 01 - Job# 123459
周二 - 03/26 - 02 - Job# 123460
周二 - 03/26 - 03 - Job# 123461
周二 - 03/26 - 03 - Job# 123462
周二 - 03/26 - 04 - Job# 123463
周二 - 03/26 - 05 - Empty
...

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