LEFT JOIN、SUM()、GROUP BY多表求和错误问题求助

直接将多个与Product_Tbl为一对多关系的明细表（采购、采购退回、销售、销售退回）同时左连接到主表，会产生笛卡尔积：比如某商品有2条采购记录、3条销售记录，连接后会生成2×3=6条临时记录，求和时每条采购数量会被重复计算3次，销售数量被重复计算2次，最终导致统计结果翻倍/多倍。

先对每个明细表按Prd_ID单独分组汇总，再将这些汇总结果左连接到Product_Tbl，避免笛卡尔积的影响：

SELECT 
    p.Prd_ID,
    p.Prd_Code,
    p.Prd_Name,
    c.CatName,
    u.UnitName,
    ISNULL(b.sumBuyQty, 0) AS sumBuyQty,
    ISNULL(b.sumBuyTotalAmount, 0) AS sumBuyTotalAmount,
    ISNULL(rb.sumR_BuyQty, 0) AS sumR_BuyQty,
    ISNULL(rb.sumR_BuyTotalAmount, 0) AS sumR_BuyTotalAmount,
    ISNULL(s.sumSaleQty, 0) AS sumSaleQty,
    ISNULL(s.sumSaleTotalAmount, 0) AS sumSaleTotalAmount,
    ISNULL(rs.sumRe_SaleQty, 0) AS sumRe_SaleQty,
    ISNULL(rs.sumRe_SaleTotalAmount, 0) AS sumRe_SaleTotalAmount
FROM dbo.Product_Tbl p
LEFT JOIN dbo.Cat_Tbl c ON p.Cat_ID = c.Cat_ID
LEFT JOIN dbo.Unit_Tbl u ON p.Unit_ID = u.Unit_ID
-- 采购明细汇总
LEFT JOIN (
    SELECT Prd_ID,
           SUM(Qty) AS sumBuyQty,
           SUM(TotalAmount) AS sumBuyTotalAmount
    FROM dbo.Buy_Details_Tbl
    GROUP BY Prd_ID
) b ON p.Prd_ID = b.Prd_ID
-- 采购退回明细汇总
LEFT JOIN (
    SELECT Prd_ID,
           SUM(Qty) AS sumR_BuyQty,
           SUM(TotalAmount) AS sumR_BuyTotalAmount
    FROM dbo.R_BuyDetails_Tbl
    GROUP BY Prd_ID
) rb ON p.Prd_ID = rb.Prd_ID
-- 销售明细汇总
LEFT JOIN (
    SELECT Prd_ID,
           SUM(Qty) AS sumSaleQty,
           SUM(TotalAmount) AS sumSaleTotalAmount
    FROM dbo.Sale_Details_Tbl
    GROUP BY Prd_ID
) s ON p.Prd_ID = s.Prd_ID
-- 销售退回明细汇总
LEFT JOIN (
    SELECT Prd_ID,
           SUM(Qty) AS sumRe_SaleQty,
           SUM(TotalAmount) AS sumRe_SaleTotalAmount
    FROM dbo.Re_Sale_Details_Tbl
    GROUP BY Prd_ID
) rs ON p.Prd_ID = rs.Prd_ID

• 每个子查询先完成单表的分组统计，确保每个商品只返回一条汇总记录，再与主表连接就不会产生重复数据。
• 使用ISNULL处理无对应记录的情况，保证统计结果显示为0而非NULL。

内容的提问来源于stack exchange，提问作者عبد الواحد