MySQL统计员工各任务执行天数并生成年度交叉表

问题背景 #

需要基于schedule表生成员工年度任务执行天数的交叉表，表结构及示例数据如下：

期望生成的2023年度交叉表结果：

原查询仅统计任务条目数，无法得到实际执行天数，需调整查询逻辑。

单个员工单个任务的天数计算 #

要计算单员工单任务的总执行天数，需先计算每条任务的有效天数（包含开始和结束日），再累加求和：

SELECT SUM(DATEDIFF(endDate, startDate) + 1) AS total_days
FROM schedule
WHERE employee = 'Mr. Anderson' 
  AND task = 'ABC'
  AND startDate >= '2023-01-01' 
  AND endDate <= '2023-12-31';

• DATEDIFF(endDate, startDate)返回两个日期的天数差（不含结束日），加1后得到包含起止日的单条任务天数。
• 用SUM()累加该员工该任务的所有记录天数，得到总执行天数。

生成交叉表（模拟PIVOT） #

MySQL无原生PIVOT函数，可通过条件聚合实现交叉表效果，避免逐条查询：

SELECT 
    employee AS Employee,
    SUM(CASE WHEN task = 'ABC' THEN DATEDIFF(endDate, startDate) + 1 ELSE 0 END) AS ABC,
    SUM(CASE WHEN task = 'DEF' THEN DATEDIFF(endDate, startDate) + 1 ELSE 0 END) AS DEF
FROM schedule
WHERE startDate >= '2023-01-01' 
  AND endDate <= '2023-12-31'
GROUP BY employee
ORDER BY employee;

逻辑说明 #

1. 按employee分组，确保每个员工占一行数据。
2. 对每个任务类型（如ABC、DEF），用CASE语句筛选对应任务并计算总天数；无该任务时返回0，保证列完整性。
3. WHERE子句限制统计的年度范围，可根据需求调整日期参数。

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