多表关联求和异常：如何修正SQL查询获取准确销售与成本值？

你遇到的核心问题是多表一对多关联产生的笛卡尔积：当同时把table_quoted_products（一个报价对应多个产品）和table_estimate（一个报价对应多条估算记录）关联到table_quotation时，数据库会生成这两个表记录的笛卡尔积（比如1个报价有3个产品，每个产品对应2条估算，就会生成3*2=6条重复的基础记录），最终导致SUM的结果被重复相乘，数值偏大。

解决思路是先分别计算每个报价单的销售总额和成本总额，再将这些聚合结果关联到主查询，避免笛卡尔积的产生。

修改后的SQL查询 #

SELECT
    a.quote_id,
    a.check_status,
    a.approve_status,
    b.title AS project,
    c.customer,
    COALESCE(sale_total.sale, 0) AS sale,
    COALESCE(cost_total.cost, 0) AS cost
FROM table_quotation a
JOIN table_project b ON a.project_id = b.project_id
JOIN table_customer c ON a.customer_id = c.id
-- 关联预先计算好的销售总额子查询
LEFT JOIN (
    SELECT quote_id, SUM(quantity * quoted_rate) AS sale
    FROM table_quoted_product
    GROUP BY quote_id
) sale_total ON a.quote_id = sale_total.quote_id
-- 关联预先计算好的成本总额子查询（仅取ref_type=2的报价产品估算）
LEFT JOIN (
    SELECT ref_id AS quote_id, SUM(qty * rate) AS cost
    FROM table_estimate
    WHERE ref_type = 2
    GROUP BY ref_id
) cost_total ON a.quote_id = cost_total.quote_id
WHERE a.approve_status = 0
GROUP BY a.quote_id, a.check_status, a.approve_status, b.title, c.customer;

关键说明 #

1. 子查询聚合避免笛卡尔积：
   • sale_total子查询先按quote_id聚合，计算每个报价的总销售额，确保每个报价只有一条销售记录。
   • cost_total子查询同样按ref_id（对应报价单ID）聚合，筛选ref_type=2的记录后计算总成本，每个报价也只有一条成本记录。
2. 使用LEFT JOIN替代INNER JOIN：确保即使某个报价没有产品或估算记录，也能正常返回（用COALESCE将NULL转为0，保证数值的完整性）。
3. GROUP BY字段规范：如果你的SQL模式开启了ONLY_FULL_GROUP_BY，需要把SELECT中所有非聚合字段都加入GROUP BY（比如a.check_status、a.approve_status等），避免语法错误。

这样修改后，就能得到准确的销售总额和成本总额，不会出现重复计算的问题。

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