3表关联+2聚合函数SQL查询：求和结果异常问题排查

错误原因 #

你的查询直接关联三张表后进行聚合，会因为CUTLOG中同一PARTID对应多条记录，导致PARTS表的行被重复匹配，最终SUM(P.PARTQUANTITY)被多次累加，结果偏大。比如某PART有2条切割日志，该PART的PARTQUANTITY会被计算2次，而非1次。

正确查询写法 #

先分别对PARTS和CUTLOG进行子查询聚合，再关联JOBS表，避免行重复问题：

SELECT
    J.CUSTOMER_NAME,
    COALESCE(P.SUM_PARTQUANTITY, 0) AS SUM_OF_PARTQUANTITY,
    COALESCE(L.SUM_LOGQUANTITY, 0) AS SUM_OF_LOGQUANTITY
FROM JOBS J
LEFT JOIN (
    -- 先按JOBID聚合PARTS的数量
    SELECT JOBID, SUM(PARTQUANTITY) AS SUM_PARTQUANTITY
    FROM PARTS
    GROUP BY JOBID
) P ON J.JOBID = P.JOBID
LEFT JOIN (
    -- 先按PARTID聚合CUTLOG的数量，再按JOBID汇总
    SELECT 
        P.JOBID, 
        SUM(L.LOGQUANTITY) AS SUM_LOGQUANTITY
    FROM CUTLOG L
    JOIN PARTS P ON L.PARTID = P.PARTID
    GROUP BY P.JOBID
) L ON J.JOBID = L.JOBID

说明 #

1. 子查询P先计算每个JOB对应的PART数量总和，避免后续关联时被重复计算
2. 子查询L先关联PARTS和CUTLOG，按PARTID聚合日志数量后，再按JOBID汇总得到每个JOB的日志总量
3. 使用COALESCE确保即使没有对应数据时，结果显示0而非NULL

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