MySQL查询求助：按日期统计员工各任务周/月工时（查询返回空集）

我有如下任务表：

需要按日期统计每位员工各任务的每周总工时（分为week1至week5）以及每月总工时，期望输出如下：

但我编写的MySQL CTE查询返回空结果集，且表中并非每天都同时存在A、B任务，恳请帮忙修正该SQL查询。我的SQL查询如下：

WITH weekly_hours_1 AS (
  SELECT 
    employee_id, 
    task, 
    SUM(hours) AS week1_hours 
  FROM 
    employee_id 
  WHERE 
    DATE BETWEEN '2023-01-01' 
    AND '2023-01-01' 
  GROUP BY 
    task
), 
weekly_hours_2 AS (
  SELECT 
    employee_id, 
    task, 
    SUM(hours) AS week2_hours 
  FROM 
    employee_id 
  WHERE 
    DATE BETWEEN '2023-01-02' 
    AND '2023-01-08' 
  GROUP BY 
    task
), 
weekly_hours_3 AS (
  SELECT 
    employee_id, 
    task, 
    SUM(hours) AS week3_hours 
  FROM 
    employee_id 
  WHERE 
    DATE BETWEEN '2023-01-09' 
    AND '2023-01-15' 
  GROUP BY 
    task
), 
weekly_hours_4 AS (
  SELECT 
    employee_id, 
    task, 
    SUM(hours) AS week4_hours 
  FROM 
    employee_id 
  WHERE 
    DATE BETWEEN '2023-01-16' 
    AND '2023-01-22' 
  GROUP BY 
    task
), 
weekly_hours_5 AS (
  SELECT 
    employee_id, 
    task, 
    SUM(hours) AS week5_hours 
  FROM 
    employee_id 
  WHERE 
    DATE BETWEEN '2023-01-23' 
    AND '2023-01-29' 
  GROUP BY 
    task
), 
monthly_hours AS (
  SELECT 
    employee_id, 
    task, 
    SUM(hours) AS monthly_hours 
  FROM 
    employee_id 
  WHERE 
    DATE BETWEEN '2022-12-22' 
    AND '2022-12-24' 
  GROUP BY 
    task
) 
SELECT 
  monthly_hours.employee_id, 
  monthly_hours.task, 
  monthly_hours.m_hours, 
  weekly_hours_1.w_hours, 
  weekly_hours_2.w_hours, 
  weekly_hours_3.w_hours, 
  weekly_hours_4.w_hours, 
  weekly_hours_5.w_hours 
FROM 
  monthly_hours 
  JOIN weekly_hours_1 ON weekly_hours_1.employee_id = monthly_hours.employee_id 
  AND monthly_hours.task = weekly_hours_1.task 
  JOIN weekly_hours_2 ON weekly_hours_2.employee_id = monthly_hours.employee_id 
  AND monthly_hours.task = weekly_hours_2.task 
  JOIN weekly_hours_3 ON weekly_hours_3.employee_id = monthly_hours.employee_id 
  AND monthly_hours.task = weekly_hours_3.task 
  JOIN weekly_hours_4 ON weekly_hours_4.employee_id = monthly_hours.employee_id 
  AND monthly_hours.task = weekly_hours_4.task 
  JOIN weekly_hours_5 ON weekly_hours_5.employee_id = monthly_hours.employee_id 
  AND monthly_hours.task = weekly_hours_5.task 
WHERE 
  weekly_hours_1.employee_id IN (1, 2, 3) 
GROUP BY 
  monthly_hours.task_id

1. 表名错误：所有CTE中FROM employee_id，误将字段名当成了任务表的实际表名。
2. 分组逻辑错误：每个CTE仅按task分组，缺少employee_id，会导致同一任务不同员工的结果被合并，或触发SQL模式报错。
3. 日期范围错误：monthly_hours的日期范围是2022年12月，与目标统计的2023年1月数据完全不匹配，导致该CTE返回空结果，最终整个查询无输出。
4. 字段引用错误：主查询中引用的m_hours、w_hours等字段，与CTE中定义的monthly_hours、week1_hours等字段名不匹配。
5. JOIN类型错误：使用内连接会过滤掉某周无工时的任务记录，导致结果缺失；需改用左连接并将NULL转为0。
6. GROUP BY字段错误：主查询中GROUP BY monthly_hours.task_id，但monthly_hours中不存在task_id字段。

这里提供两种更简洁可靠的实现方式：

方式一：条件聚合（推荐） #

无需多个CTE，直接通过CASE WHEN完成各周工时统计：

SELECT
  employee_id,
  task,
  SUM(CASE WHEN date BETWEEN '2023-01-01' AND '2023-01-01' THEN hours ELSE 0 END) AS week1_hours,
  SUM(CASE WHEN date BETWEEN '2023-01-02' AND '2023-01-08' THEN hours ELSE 0 END) AS week2_hours,
  SUM(CASE WHEN date BETWEEN '2023-01-09' AND '2023-01-15' THEN hours ELSE 0 END) AS week3_hours,
  SUM(CASE WHEN date BETWEEN '2023-01-16' AND '2023-01-22' THEN hours ELSE 0 END) AS week4_hours,
  SUM(CASE WHEN date BETWEEN '2023-01-23' AND '2023-01-29' THEN hours ELSE 0 END) AS week5_hours,
  SUM(hours) AS monthly_hours
-- 替换为你的任务表实际表名
FROM employee_tasks
WHERE date BETWEEN '2023-01-01' AND '2023-01-31'
  AND employee_id IN (1, 2, 3)
GROUP BY employee_id, task
ORDER BY employee_id, task;

方式二：CTE+左连接 #

如果偏好CTE结构，可使用以下修正版本：

WITH weekly_hours AS (
  SELECT
    employee_id,
    task,
    CASE
      WHEN date BETWEEN '2023-01-01' AND '2023-01-01' THEN 'week1'
      WHEN date BETWEEN '2023-01-02' AND '2023-01-08' THEN 'week2'
      WHEN date BETWEEN '2023-01-09' AND '2023-01-15' THEN 'week3'
      WHEN date BETWEEN '2023-01-16' AND '2023-01-22' THEN 'week4'
      WHEN date BETWEEN '2023-01-23' AND '2023-01-29' THEN 'week5'
      ELSE 'other'
    END AS week_num,
    SUM(hours) AS week_hours
  -- 替换为你的任务表实际表名
  FROM employee_tasks
  WHERE date BETWEEN '2023-01-01' AND '2023-01-31'
  GROUP BY employee_id, task, week_num
),
monthly_hours AS (
  SELECT
    employee_id,
    task,
    SUM(hours) AS monthly_hours
  -- 替换为你的任务表实际表名
  FROM employee_tasks
  WHERE date BETWEEN '2023-01-01' AND '2023-01-31'
  GROUP BY employee_id, task
)
SELECT
  m.employee_id,
  m.task,
  COALESCE(w1.week_hours, 0) AS week1_hours,
  COALESCE(w2.week_hours, 0) AS week2_hours,
  COALESCE(w3.week_hours, 0) AS week3_hours,
  COALESCE(w4.week_hours, 0) AS week4_hours,
  COALESCE(w5.week_hours, 0) AS week5_hours,
  m.monthly_hours
FROM monthly_hours m
LEFT JOIN weekly_hours w1 ON m.employee_id = w1.employee_id AND m.task = w1.task AND w1.week_num = 'week1'
LEFT JOIN weekly_hours w2 ON m.employee_id = w2.employee_id AND m.task = w2.task AND w2.week_num = 'week2'
LEFT JOIN weekly_hours w3 ON m.employee_id = w3.employee_id AND m.task = w3.task AND w3.week_num = 'week3'
LEFT JOIN weekly_hours w4 ON m.employee_id = w4.employee_id AND m.task = w4.task AND w4.week_num = 'week4'
LEFT JOIN weekly_hours w5 ON m.employee_id = w5.employee_id AND m.task = w5.task AND w5.week_num = 'week5'
WHERE m.employee_id IN (1, 2, 3)
ORDER BY m.employee_id, m.task;

• 替换了错误的表名，需根据你的实际表名调整
• 修正了日期范围，确保统计2023年1月的数据
• 按employee_id和task正确分组，保证每个员工的每个任务单独统计
• 用条件聚合或左连接+COALESCE处理无工时的周，返回0而非NULL
• 简化查询逻辑，避免冗余CTE和错误连接

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