MySQL三表Join计算订单金额结果异常问题排查

你的问题出在直接连接两个一对多关系的表（products_request和products_response都对应products_header_response的单条记录），这会产生笛卡尔积，让每条请求记录和每条确认记录都匹配一次，最终聚合的时候重复计算了多次数值，导致金额和数量翻倍/多倍。

比如你的示例数据：

• products_request有7条记录
• products_response有2条符合WHERE条件的记录
  连接后会生成7×2=14条记录，所以求和时每个请求的数量/金额会被计算2次，每个确认的数量/金额会被计算7次，完全对应你看到的错误结果（请求金额274→548，确认金额93.06→651.42，请求数量12→24）。

解决思路是先分别对请求表和确认表按订单聚合，再和表头表连接，避免笛卡尔积：

SELECT 
    resp.order_number AS Order_id,
    req_agg.Req_Order_Amnt AS Tot_req_Qty_amount,
    resp_agg.Tot_confirmed_Qty_amount,
    req_agg.Requested_products
FROM products_header_response resp
-- 先聚合请求表的总金额和总数量
LEFT JOIN (
    SELECT 
        edi_order_id,
        ROUND(SUM(requested_quantity * net_price), 2) AS Req_Order_Amnt,
        SUM(requested_quantity) AS Requested_products
    FROM products_request
    GROUP BY edi_order_id
) req_agg ON req_agg.edi_order_id = resp.order_number
-- 先聚合确认表的总金额
LEFT JOIN (
    SELECT 
        edi_order_response_id,
        ROUND(SUM(
            COALESCE(dispatch_quantity, 0) * COALESCE(net_price, 0) + 
            COALESCE(backorder_quantity, 0) * COALESCE(net_price, 0)
        ), 2) AS Tot_confirmed_Qty_amount
    FROM products_response
    WHERE COALESCE(dispatch_quantity, '') != '' OR COALESCE(backorder_quantity, '') != ''
    GROUP BY edi_order_response_id
) resp_agg ON resp_agg.edi_order_response_id = resp.id
WHERE DATE(resp.orderdate) = '20180411'
-- 过滤掉既无请求也无确认的订单（可选，根据业务调整）
AND (req_agg.edi_order_id IS NOT NULL OR resp_agg.edi_order_response_id IS NOT NULL)

1. 先聚合再连接：
   分别对products_request和products_response按订单维度聚合，得到每个订单的请求总金额、总数量，以及确认总金额，再和表头表关联，彻底避免笛卡尔积。

2. 空值处理：
   使用COALESCE把空字符串/NULL转换为0，避免空值参与计算时得到NULL，影响求和结果。比如COALESCE(dispatch_quantity, 0)会把空的发货数量转为0，确保乘法计算正常。

3. GROUP BY合规性：
   原查询中SELECT包含req.requested_quantity但GROUP BY只指定了items_resp.edi_order_response_id，这在严格SQL模式下会报错（非聚合字段必须出现在GROUP BY中），修正后的查询完全符合SQL标准。

用你提供的示例数据测试，这个查询会返回预期结果：

（注：你的预期结果中确认金额写的是93.96，可能是示例数据笔误，按实际计算应为93.06）

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