MySQL按任务类型统计工作日：带非工作日排除的DATEDIFF计算

问题背景 #

现有@MatBailie提供的MySQL查询语句，可统计指定时段内员工各任务的总天数：

SELECT
  taskname,
  employee,
  SUM(
    DATEDIFF(
      LEAST(     enddate, '2023-12-31'),
      GREATEST(startdate, '2023-01-01')
    )
    +1
  ) AS total_days
FROM
  schedule
WHERE
  startDate <= '2023-12-31'
  AND
  endDate   >= '2023-01-01'
GROUP BY
  employee,
  taskname

该查询运行正常，但需针对部分任务（参考activities表中count_non_working_days=0的任务），从统计结果中扣除周末及自定义节假日。例如员工请假时段包含的周末、节假日不应计入请假天数。

要求实现支持根据任务类型切换是否扣除非工作日，且可纳入PHP数组存储的自定义节假日的MySQL语句。

示例数据 #

• schedule表：包含员工任务记录，如Anderson先生的Programming和Vacation任务
• activities表：定义Programming的count_non_working_days=1（周末计入统计），Vacation的count_non_working_days=0（周末不计入统计）
• 预期结果：Programming共11天、Vacation共12天

实现方案 #

步骤1：导入自定义节假日到临时表 #

由于自定义节假日存储在PHP数组中，先将其导入MySQL临时表，方便后续查询：

-- 创建临时表存储节假日
CREATE TEMPORARY TABLE IF NOT EXISTS holidays (
  holiday_date DATE PRIMARY KEY
);

-- 从PHP数组插入数据（实际需结合PHP代码生成对应INSERT语句）
INSERT INTO holidays (holiday_date) VALUES
('2023-01-01'),
('2023-01-23'),
-- 补充其他自定义节假日
('2023-12-25');

步骤2：编写最终统计查询 #

结合任务类型判断，计算符合要求的有效天数：

SELECT
  s.taskname,
  s.employee,
  SUM(
    CASE
      -- 任务需计入非工作日时，直接统计时段总天数
      WHEN a.count_non_working_days = 1 THEN
        DATEDIFF(LEAST(s.enddate, '2023-12-31'), GREATEST(s.startdate, '2023-01-01')) + 1
      -- 任务需扣除非工作日时，计算总天数减去周末和节假日数量
      ELSE
        (DATEDIFF(LEAST(s.enddate, '2023-12-31'), GREATEST(s.startdate, '2023-01-01')) + 1)
        -- 扣除时段内的周末天数
        - FLOOR((DATEDIFF(LEAST(s.enddate, '2023-12-31'), GREATEST(s.startdate, '2023-01-01')) + 1) / 7) * 2
        - CASE WHEN WEEKDAY(GREATEST(s.startdate, '2023-01-01')) = 6 THEN 1 ELSE 0 END
        - CASE WHEN WEEKDAY(LEAST(s.enddate, '2023-12-31')) = 5 THEN 1 ELSE 0 END
        -- 扣除时段内非周末的节假日天数（避免重复扣除）
        - (
          SELECT COUNT(*)
          FROM holidays h
          WHERE h.holiday_date BETWEEN GREATEST(s.startdate, '2023-01-01') AND LEAST(s.enddate, '2023-12-31')
          AND WEEKDAY(h.holiday_date) NOT IN (5,6)
        )
    END
  ) AS total_days
FROM
  schedule s
LEFT JOIN
  activities a ON a.name = s.taskname
WHERE
  s.startDate <= '2023-12-31'
  AND s.endDate >= '2023-01-01'
GROUP BY
  s.employee,
  s.taskname;

逻辑说明 #

1. 周末计算：
   • 先统计时段内完整周数，每完整周扣除2天周末
   • 单独判断时段起始日是否为周日（WEEKDAY=6）、结束日是否为周六（WEEKDAY=5），额外扣除对应天数
2. 节假日扣除：
   • 统计时段内的节假日数量，同时排除本身就是周末的节假日，避免重复扣除
3. 任务类型分支：
   • 通过CASE语句根据activities.count_non_working_days的值，选择对应的天数计算逻辑

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