如何用SQL修改员工排班以展示完整日程

要实现把Shift时段拆分成穿插在休息、午餐之间的Work时段，核心思路是提取所有关键时间节点，按顺序串联生成相邻时段，具体可以通过CTE（公共表表达式）结合窗口函数实现，以下是通用SQL方案（适配大多数支持窗口函数的数据库如MySQL 8+、PostgreSQL、SQL Server等）：

完整SQL代码 #

WITH all_events AS (
    -- 提取Shift的开始和结束节点
    SELECT 
        Emp,
        Start AS event_time,
        'ShiftStart' AS event_type,
        NULL AS original_option
    FROM schedule
    WHERE Option = 'Shift'
    UNION ALL
    SELECT 
        Emp,
        End AS event_time,
        'ShiftEnd' AS event_type,
        NULL AS original_option
    FROM schedule
    WHERE Option = 'Shift'
    UNION ALL
    -- 提取Break/Lunch的开始和结束节点，保留原类型
    SELECT 
        Emp,
        Start AS event_time,
        CONCAT(Option, 'Start') AS event_type,
        Option AS original_option
    FROM schedule
    WHERE Option IN ('Break', 'Lunch')
    UNION ALL
    SELECT 
        Emp,
        End AS event_time,
        CONCAT(Option, 'End') AS event_type,
        NULL AS original_option
    FROM schedule
    WHERE Option IN ('Break', 'Lunch')
),
ordered_events AS (
    -- 按员工和时间排序，获取下一个事件时间
    SELECT 
        Emp,
        event_time,
        original_option,
        LEAD(event_time) OVER (PARTITION BY Emp ORDER BY event_time) AS next_event_time
    FROM all_events
)
-- 生成最终的排班明细
SELECT 
    Emp,
    event_time AS Start,
    next_event_time AS End,
    CASE
        WHEN original_option IS NOT NULL THEN original_option
        ELSE 'Work'
    END AS Option
FROM ordered_events
WHERE next_event_time IS NOT NULL -- 排除Shift结束的节点（无后续时间）
ORDER BY Emp, Start;

代码解释 #

1. all_events CTE：把所有关键时间点拆分成独立事件，包括：
   • Shift的开始和结束时间
   • 每个Break/Lunch的开始和结束时间，同时保留原休息/午餐的类型标记
2. ordered_events CTE：对每个员工的事件按时间排序，用LEAD()窗口函数获取当前事件的下一个时间点，这样就能得到相邻两个时间点组成的时段
3. 最终查询：
   • 当事件是Break/Lunch的开始时，直接使用原original_option生成对应记录
   • 其余情况（Shift开始、休息/午餐结束）生成Work时段
   • 过滤掉没有后续时间的节点（也就是Shift的结束时间）

注意事项 #

• 如果你的数据库不支持CTE（比如MySQL 5.x），可以把CTE替换成临时表或者子查询
• 确保Start和End字段是时间类型（如TIME），如果是字符串类型，需要先转换为时间类型再排序，避免排序错误

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