多表数值计算问题：Store与OrderDetail表数据汇总错误排查

你的SQL语句求和结果不符合预期的核心原因是直接关联未汇总的Store和OrderDetail表会产生笛卡尔积，导致同一条库存记录或订单记录被多次匹配，求和时数值被重复累加。

比如Banana在Store中有2条记录，OrderDetail中有2条记录，关联后会生成4条匹配记录，此时Sum(Store.Qty)会计算为5+5+2+2=14（而非期望的7），Sum(OrderDetail.Qty)会计算为1+2+1+2=6（而非期望的3），完全偏离预期结果。

正确解法 #

正确思路是先分别对两个表按ProductId进行汇总，再将汇总结果关联，彻底避免重复计算。以下是两种可行写法：

方法1：子查询关联 #

SELECT
    p.Name,
    COALESCE(s.TotalStoreQty, 0) AS Store,
    COALESCE(o.TotalOrderQty, 0) AS QtyOrder,
    COALESCE(o.TotalAmount, 0.00) AS Amount
FROM Product p
LEFT JOIN (
    -- 先按产品汇总库存数量
    SELECT ProductId, SUM(Qty) AS TotalStoreQty
    FROM Store
    GROUP BY ProductId
) s ON p.ProductId = s.ProductId
LEFT JOIN (
    -- 先按产品汇总订单数量与金额
    SELECT ProductId, SUM(Qty) AS TotalOrderQty, SUM(Amount) AS TotalAmount
    FROM OrderDetail
    GROUP BY ProductId
) o ON p.ProductId = o.ProductId
ORDER BY p.Name;

方法2：CTE（公共表表达式）写法（更易读） #

WITH StoreSummary AS (
    SELECT ProductId, SUM(Qty) AS TotalStoreQty
    FROM Store
    GROUP BY ProductId
),
OrderSummary AS (
    SELECT ProductId, SUM(Qty) AS TotalOrderQty, SUM(Amount) AS TotalAmount
    FROM OrderDetail
    GROUP BY ProductId
)
SELECT
    p.Name,
    COALESCE(s.TotalStoreQty, 0) AS Store,
    COALESCE(o.TotalOrderQty, 0) AS QtyOrder,
    COALESCE(o.TotalAmount, 0.00) AS Amount
FROM Product p
LEFT JOIN StoreSummary s ON p.ProductId = s.ProductId
LEFT JOIN OrderSummary o ON p.ProductId = o.ProductId
ORDER BY p.Name;

结果说明 #

• 使用LEFT JOIN+COALESCE可以确保即使某产品没有库存或订单，也能在结果中显示（对应数值自动替换为0）。
• 先汇总再关联的方式彻底避免了笛卡尔积，每个产品的库存、订单数值都是正确的单次求和结果，最终输出与你期望的结果完全一致。

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