SQL Server如何关联日期函数生成表与班次表展示员工每日排班休息情况

核心思路 #

你之前的查询缺少「全量员工+指定范围全量日期」的基础数据集，仅直接关联日期表与班次表，自然只会返回存在排班记录的匹配行。我们可以通过交叉连接生成全量员工日期基准表，再左连接班次表的方式补全休息日的行。

实现代码 #

标准实现（依赖独立员工主表，推荐） #

假设你用到的表/函数信息如下：

• 自定义日期函数：dbo.GetDateRange(@StartDate, @EndDate)，返回日期字段CalendarDate
• 员工主表：Employee，员工唯一标识字段EmployeeID
• 员工班次表：EmployeeShift，关联字段EmployeeID、ShiftDate，班次信息字段ShiftInfo

-- 定义查询的起止日期
DECLARE @StartDate DATE = '2024-01-01', @EndDate DATE = '2024-01-31'

SELECT
    e.EmployeeID,
    c.CalendarDate,
    ISNULL(s.ShiftInfo, '休息') AS ShiftStatus
    -- 可额外添加e.EmployeeName、e.Department等员工字段
FROM Employee e
-- 交叉连接全量日期，生成所有员工对应所有日期的基准行
CROSS JOIN dbo.GetDateRange(@StartDate, @EndDate) c
-- 左连接班次表匹配排班信息
LEFT JOIN EmployeeShift s
    ON e.EmployeeID = s.EmployeeID
    AND c.CalendarDate = s.ShiftDate
ORDER BY e.EmployeeID, c.CalendarDate

替代实现（无独立员工主表） #

如果没有单独维护的员工主表，也可以从班次表去重获取所有员工ID，缺点是查询周期内完全没有排班的员工不会出现在结果中：

DECLARE @StartDate DATE = '2024-01-01', @EndDate DATE = '2024-01-31'

SELECT
    e.EmployeeID,
    c.CalendarDate,
    ISNULL(s.ShiftInfo, '休息') AS ShiftStatus
FROM (
    -- 从班次表去重提取所有出现过的员工ID
    SELECT DISTINCT EmployeeID FROM EmployeeShift
) e
CROSS JOIN dbo.GetDateRange(@StartDate, @EndDate) c
LEFT JOIN EmployeeShift s
    ON e.EmployeeID = s.EmployeeID
    AND c.CalendarDate = s.ShiftDate
ORDER BY e.EmployeeID, c.CalendarDate

注意事项 #

• 日期匹配条件必须写在LEFT JOIN的ON子句中，不要写在WHERE子句里，否则会过滤掉没有排班的行，效果等同于内连接
• 如果同一员工同一日期存在多条排班记录，可根据需求通过GROUP BY、DISTINCT或者调整关联逻辑去重
• 如需筛选特定员工范围，直接在Employee表的查询部分增加WHERE条件即可，可有效减少计算量

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