MySQL根据起止日期周几规则生成指定周员工排班表

核心逻辑 #

你已经有预生成的日历表，不需要额外做日期生成，直接关联两张表即可，核心关联逻辑是匹配周几+规则生效日期范围：

• 第一步：从日历表过滤出指定年度、周数的所有日期
• 第二步：将过滤后的日历数据和排班规则表关联，匹配两个条件：
  1. 日历的dayofweek和排班规则的weekday值相等
  2. 日历的日期落在排班规则的start_date和end_date生效区间内
• 第三步：按日期、员工ID排序输出即可

示例SQL #

假设排班规则表名为employee_shift_rules，日历表名为calendar，查询2021年第40周排班明细的SQL如下：

SELECT 
    c.fulldate AS `date`,
    r.employee,
    r.weekday,
    r.shift_start,
    r.shift_end
FROM 
    calendar c
INNER JOIN 
    employee_shift_rules r 
    ON c.dayofweek = r.weekday
    AND c.fulldate >= r.start_date
    AND c.fulldate <= r.end_date
WHERE 
    c.year = 2021 
    AND c.week = 40
ORDER BY 
    c.fulldate ASC, r.employee ASC;

注意事项&优化建议 #

• 确保日历表的week字段周数计算规则和你的业务定义一致（比如一周起始是周一还是周日），预生成日历表时已经统一规则的话无需额外处理
• 可以给calendar表加(year, week)联合索引，给employee_shift_rules表加(start_date, end_date, weekday)联合索引，大幅提升查询效率
• 如果需要查询多个周的排班，只需要修改WHERE条件的year和week过滤范围即可

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