MySQL多表LEFT JOIN关联查询SUM求和结果错误解决方法

你写的SQL返回结果错误，核心是三个问题：

• 多表直接关联产生笛卡尔积：同一个产品在销售、采购、销售退回表中如果分别存在多条明细记录，直接左连会产生行数的乘法膨胀，比如某产品有2条销售明细、3条采购明细，连表后会生成2*3=6条重复记录，此时直接对quantity求和会重复计算，结果远大于实际值。
• 分组逻辑不合法：SELECT列表中包含product_id、产品编码、产品名称多个字段，却仅按产品名称pname分组，一旦存在重名产品，会把不同产品的统计数据合并，同时这种写法在大部分SQL严格模式下会直接报语法错误。
• 字段选取冗余：同时从三个业务表取product_id字段没有实际意义，三个字段的取值本就应该和products表主键id对齐，重复选取反而容易引发分组异常。

要避免笛卡尔积导致的求和错误，需要先在子查询中对每个业务表单独按产品ID完成聚合，再和产品主表关联，保证关联时每个产品在每个子查询中最多匹配1条记录，不会产生行膨胀。

SELECT
    p.id AS Product_id,
    p.code AS pcode,
    p.name AS pname,
    IFNULL(s.SUM_SALE_DETAILS, 0) AS SUM_SALE_DETAILS,
    IFNULL(pur.SUM_PURCHASE_DETAILS, 0) AS SUM_PURCHASE_DETAILS,
    IFNULL(sr.SUM_SALE_RETURN_DETAILS, 0) AS SUM_SALE_RETURN_DETAILS
FROM products p
LEFT JOIN (
    SELECT product_id, SUM(quantity) AS SUM_SALE_DETAILS
    FROM sale_details
    GROUP BY product_id
) s ON p.id = s.product_id
LEFT JOIN (
    SELECT product_id, SUM(quantity) AS SUM_PURCHASE_DETAILS
    FROM purchase_details
    GROUP BY product_id
) pur ON p.id = pur.product_id
LEFT JOIN (
    SELECT product_id, SUM(quantity) AS SUM_SALE_RETURN_DETAILS
    FROM sale_return_details
    GROUP BY product_id
) sr ON p.id = sr.product_id
ORDER BY SUM_SALE_RETURN_DETAILS DESC;

说明 #

• 用IFNULL函数处理无业务记录的场景，把NULL值转换为0，统计结果更直观
• 所有聚合逻辑在子查询内完成，从根源上避免多表关联产生的重复计算问题
• 以products表的唯一主键id作为关联依据，不会出现重名产品数据混淆的问题
• 返回字段完全覆盖要求的Product_id | SUM_SALE_DETAILS | SUM_PURCHASE_DETAILS | SUM_SALE_RETURN_DETAILS格式，同时保留了产品编码、名称字段方便查看。

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