多关联表求和重复计算问题：求各Job的总预估工时

表结构说明 #

Jobs表 #

Levels表 #

SubLevels表 #

DoorTasks表 #

DoorFrameTasks表 #

DoorHardwareTasks表 #

当前问题 #

原查询直接多表左连接后求和，会因笛卡尔积导致工时重复累加：

SELECT 
    j.JobName,
    SUM(dt.EstimatedHours) + SUM(df.EstimatedHours) + SUM(dh.EstimatedHours) AS "Total Estimated Hours"
FROM  
    Jobs AS j 
LEFT OUTER JOIN
    Levels AS l ON l.JobId = j.JobId 
LEFT OUTER JOIN 
    SubLevels AS sl ON sl.LevelId = l.LevelId 
LEFT OUTER JOIN
    DoorTasks AS dt ON dt.SubLevelId = sl.SubLevelId 
LEFT OUTER JOIN
    DoorFrameTasks AS df ON df.SubLevelId = sl.SubLevelId 
LEFT OUTER JOIN
    DoorHardwareTasks AS dh ON dh.SubLevelId = sl.SubLevelId         
GROUP BY
    j.JobName

比如SubLevelId=1下有3条DoorTasks、3条DoorFrameTasks，连接后会产生9条记录，每条DoorTask的工时会被重复计算3次，最终总和远大于实际值。

解决方案 #

核心思路：先在任务表层面按SubLevel聚合求和，再关联到Job层级汇总，彻底避免笛卡尔积问题。

方法一：子查询预聚合各任务表的SubLevel工时 #

SELECT
    j.JobName,
    COALESCE(SUM(sub.TotalDoorHours), 0) +
    COALESCE(SUM(sub.TotalFrameHours), 0) +
    COALESCE(SUM(sub.TotalHardwareHours), 0) AS "Total Estimated Hours"
FROM Jobs j
LEFT JOIN Levels l ON l.JobId = j.JobId
LEFT JOIN SubLevels sl ON sl.LevelId = l.LevelId
LEFT JOIN (
    SELECT SubLevelId, SUM(EstimatedHours) AS TotalDoorHours
    FROM DoorTasks
    GROUP BY SubLevelId
) dt ON dt.SubLevelId = sl.SubLevelId
LEFT JOIN (
    SELECT SubLevelId, SUM(EstimatedHours) AS TotalFrameHours
    FROM DoorFrameTasks
    GROUP BY SubLevelId
) df ON df.SubLevelId = sl.SubLevelId
LEFT JOIN (
    SELECT SubLevelId, SUM(EstimatedHours) AS TotalHardwareHours
    FROM DoorHardwareTasks
    GROUP BY SubLevelId
) dh ON dh.SubLevelId = sl.SubLevelId
GROUP BY j.JobName;

方法二：CTE统一聚合所有任务类型的SubLevel工时 #

WITH SubLevelTaskTotals AS (
    SELECT SubLevelId, SUM(EstimatedHours) AS TaskHours
    FROM DoorTasks
    GROUP BY SubLevelId
    UNION ALL
    SELECT SubLevelId, SUM(EstimatedHours) AS TaskHours
    FROM DoorFrameTasks
    GROUP BY SubLevelId
    UNION ALL
    SELECT SubLevelId, SUM(EstimatedHours) AS TaskHours
    FROM DoorHardwareTasks
    GROUP BY SubLevelId
)
SELECT
    j.JobName,
    COALESCE(SUM(slt.TaskHours), 0) AS "Total Estimated Hours"
FROM Jobs j
LEFT JOIN Levels l ON l.JobId = j.JobId
LEFT JOIN SubLevels sl ON sl.LevelId = l.LevelId
LEFT JOIN SubLevelTaskTotals slt ON slt.SubLevelId = sl.SubLevelId
GROUP BY j.JobName;

说明 #

• COALESCE用于处理无任务的Job/SubLevel，避免NULL值导致总和异常
• 两种方法均先完成SubLevel层级的工时聚合，再向上关联计算Job总工时，彻底消除重复累加问题

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