SQL三表左连接对中间表字段求和结果错误如何解决

你遇到的是典型的关联查询维度膨胀问题：size-color表和sale表是1对多的关系，直接关联后，同一条size-color记录会对应多条sale表的销售记录，导致size-color的total字段被重复计数。比如产品batman对应的size-color-id=1的total值为32，对应2条销售记录，直接关联后求和时会被计算2次得到64，最终总和就会出现错误。

最优解法是先对需要聚合的子表做预聚合，再执行关联操作，从根源上避免行重复导致的计数错误，修正后的SQL如下：

SELECT
    p.`product-name` AS name,
    IFNULL(c.total_sum, 0) AS total_sum,
    IFNULL(s.sold_sum, 0) AS sold_qty_sum
FROM products p
-- 预聚合得到每个产品的total总和
LEFT JOIN (
    SELECT `product-id`, SUM(total) AS total_sum
    FROM `size-color`
    GROUP BY `product-id`
) c ON p.id = c.`product-id`
-- 预聚合得到每个产品的销量总和
LEFT JOIN (
    SELECT sc.`product-id`, SUM(s.`sold-qty`) AS sold_sum
    FROM sale s
    INNER JOIN `size-color` sc ON s.`size-color-id` = sc.id
    GROUP BY sc.`product-id`
) s ON p.id = s.`product-id`

补充说明：

• 你原SQL中的sells表名属于笔误，上述代码已修正为实际的sale表名
• 字段名、表名中的连字符属于SQL特殊字符，需要用反引号包裹才能正常执行
• 用IFNULL处理无对应数据的场景，避免返回NULL值

最终执行后得到的正确结果如下：

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