MySQL按指定周统计工时并排序，保留无工时项目的实现方法

要实现指定周工时统计+降序排序+保留无工时项目的需求，核心是先获取所有项目编号，再左连接该周的工时统计结果，避免WHERE条件过滤掉无工时的项目。

最终SQL语句 #

SELECT 
    all_jobs.jobno,
    COALESCE(weekly_hours.weekHours, 0) AS weekHours
FROM 
    -- 提取所有存在的项目编号
    (SELECT DISTINCT jobno FROM gusTime) AS all_jobs
LEFT JOIN 
    -- 统计指定周内每个项目的工时总和
    (SELECT 
         jobno,
         SUM(hours) AS weekHours
     FROM gusTime
     WHERE date BETWEEN '2024-05-06' AND '2024-05-10'
     GROUP BY jobno) AS weekly_hours ON all_jobs.jobno = weekly_hours.jobno
ORDER BY 
    weekHours DESC;

逻辑说明 #

1. 获取全量项目：子查询all_jobs从gusTime中提取所有唯一的jobno，确保无论项目是否有工时记录，都会被纳入结果集。
2. 指定周工时统计：子查询weekly_hours仅筛选目标周的日期范围，计算每个项目的工时总和。
3. 补全无工时项目：通过左连接保留all_jobs中的所有项目，对于该周无工时的项目，weekly_hours.weekHours会返回NULL，用COALESCE将其转换为0。
4. 降序排序：最后按weekHours从高到低排序，满足需求。

原方案问题分析 #

• 第一种SQL：WHERE date>='2024-04-29'未限制到目标周，统计的是该日期之后所有周的总工时，不符合需求。
• 第二种SQL：WHERE条件直接过滤了指定周的数据，GROUP BY后仅返回该周有工时记录的项目，无工时的项目因无匹配行被排除。

优化建议（若有独立项目表） #

如果系统中存在单独的项目表（比如jobs，存储所有项目编号），建议直接从该表获取jobno，这样即使项目从未有过工时记录，也能被保留：

SELECT 
    jobs.jobno,
    COALESCE(weekly_hours.weekHours, 0) AS weekHours
FROM 
    jobs
LEFT JOIN 
    (SELECT 
         jobno,
         SUM(hours) AS weekHours
     FROM gusTime
     WHERE date BETWEEN '2024-05-06' AND '2024-05-10'
     GROUP BY jobno) AS weekly_hours ON jobs.jobno = weekly_hours.jobno
ORDER BY 
    weekHours DESC;

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