SQL关联Order与Shipping表聚合时Order_total重复求和问题求解

问题原因 #

订单表1条记录匹配到物流表2条包裹记录时，JOIN操作会把订单行复制为对应包裹数量的行数，直接对order_total求和就会出现重复统计的问题。

方案1：保留所有包裹明细，仅首行显示订单总额 #

符合你要求的输出效果，既保留所有包裹的Pkg_num、shipped_date等字段，又避免订单总额重复统计：

SELECT 
    O.Ord_num,
    O.Ord_date,
    O.Customer_name,
    P.Pkg_num,
    P.Pkg_weight,
    P.shipped_date,
    P.shipping_cost,
    -- 同一订单仅第一个包裹返回订单总额，其余返回0
    CASE 
        WHEN ROW_NUMBER() OVER(PARTITION BY O.Ord_num ORDER BY P.Pkg_num) = 1 
        THEN O.Order_total 
        ELSE 0 
    END AS adjusted_order_total
FROM Order_table O
INNER JOIN Shipping_table P ON O.Ord_num = P.Ord_num
ORDER BY O.Ord_num, P.Pkg_num;

如果需要聚合统计结果，直接对上面查询的结果求和即可：

SELECT 
    SUM(adjusted_order_total) AS total_order_amount,
    SUM(Pkg_weight) AS total_pkg_weight,
    SUM(shipping_cost) AS total_shipping_cost
FROM (
    SELECT 
        O.Order_total,
        P.Pkg_weight,
        P.shipping_cost,
        CASE WHEN ROW_NUMBER() OVER(PARTITION BY O.Ord_num ORDER BY P.Pkg_num) = 1 THEN O.Order_total ELSE 0 END AS adjusted_order_total
    FROM Order_table O
    INNER JOIN Shipping_table P ON O.Ord_num = P.Ord_num
) t;

方案2：仅需聚合结果时的更优写法 #

如果不需要保留包裹明细，仅需要统计结果，可以分别对两张表做聚合后再JOIN，性能更高：

SELECT 
    O.total_order_amount,
    P.total_pkg_weight,
    P.total_shipping_cost
FROM (
    SELECT Ord_num, SUM(Order_total) AS total_order_amount 
    FROM Order_table 
    GROUP BY Ord_num
) O
INNER JOIN (
    SELECT 
        Ord_num,
        SUM(Pkg_weight) AS total_pkg_weight,
        SUM(shipping_cost) AS total_shipping_cost 
    FROM Shipping_table 
    GROUP BY Ord_num
) P ON O.Ord_num = P.Ord_num;

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