如何用SQL按作业（job）统计各工作日序的工时总和

原始工时记录表（timesheet entries） #

统计需求 #

统计每个job_id对应的第1个工作日、第2个工作日、第3个工作日的工时总和，期望输出：

遇到的问题 #

• 同一工作日可能有多名员工作业，直接用min(date_worked)子查询会出现重复日期
• 作业的工作日不连续，无法通过最小日期加N天的方式统计后续工作日

可以通过分组汇总+窗口函数排序+条件聚合的组合方式实现，具体SQL代码如下（兼容MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库）：

WITH daily_job_hours AS (
    -- 按job和日期汇总每日总工时，合并同一日期多个员工的工时
    SELECT 
        job_id,
        date_worked,
        SUM(hours_worked) AS daily_total
    FROM timesheet_entries
    GROUP BY job_id, date_worked
),
ranked_days AS (
    -- 给每个job下的日期按先后顺序排号，最早的为Day1，次早为Day2，以此类推
    SELECT 
        job_id,
        daily_total,
        ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY date_worked) AS day_rank
    FROM daily_job_hours
)
-- 条件聚合，把不同排名的工时转成列，无数据则显示0
SELECT 
    job_id,
    COALESCE(SUM(CASE WHEN day_rank = 1 THEN daily_total END), 0) AS Day1_hours,
    COALESCE(SUM(CASE WHEN day_rank = 2 THEN daily_total END), 0) AS Day2_hours,
    COALESCE(SUM(CASE WHEN day_rank = 3 THEN daily_total END), 0) AS Day3_hours
FROM ranked_days
GROUP BY job_id
ORDER BY job_id;

代码说明 #

1. daily_job_hours CTE：先对每个job_id和date_worked分组，计算当日所有员工的工时总和，解决了同一日期多人作业的重复问题。
2. ranked_days CTE：使用ROW_NUMBER()窗口函数，按job_id分组、date_worked升序排序，给每个job下的日期分配唯一的序号（Day1对应序号1，Day2对应序号2），彻底避开了日期不连续的问题。
3. 最终查询：用CASE WHEN做条件聚合，分别统计序号1、2、3的工时总和，并用COALESCE()将NULL值转为0，完全匹配期望的输出格式。

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