MySQL多表内连接Sum()函数计算值翻倍问题及正确库存查询方法咨询

这个问题我做库存统计时也踩过一模一样的坑！核心原因是内连接引发的笛卡尔积：当一个商品同时存在多条采购记录和多条出库记录时，内连接会把每条采购记录和每条出库记录两两配对，导致求和时数据被重复计算——比如1条采购记录配2条出库记录，求和时采购数量就会被算2次，自然就出现数值翻倍的情况了。

问题出在哪？ #

你原来的查询直接把Item和两张明细表内连接，假设某商品有2条采购记录、3条出库记录，连接后会生成2×3=6条重复组合的记录，SUM(ReceivedQTY)时每个采购数量会被累加3次，SUM(QTYIssue)时每个出库数量会被累加2次，结果当然和实际值不符。

正确的查询思路 #

先分别对采购表和出库表按ItemID做聚合计算，得到每个商品的总收货量和总出库量，再把这两个聚合结果和Item表做左连接（避免漏掉没有采购/出库记录的商品），这样就不会产生笛卡尔积了。

最终查询语句 #

SELECT
    I.Item_Id,
    I.Item_Name,
    I.Specification,
    I.Item_HSN,
    I.unit,
    -- 计算可用库存：期初库存 + 总收货量 - 总出库量，用COALESCE处理NULL为0
    (I.QTY + COALESCE(PUBD_TOTAL.TotalReceived, 0) - COALESCE(ISSUE_TOTAL.TotalIssued, 0)) AS AVAILABLEQTY,
    -- 取采购单价（如果同商品存在多单价，可根据业务需求改用MAX/AVG）
    COALESCE(PUBD_TOTAL.IssueStockRate, 0) AS Rate,
    I.Serial_number_applied AS SNA,
    I.QTY AS OPENINGQTY,
    COALESCE(PUBD_TOTAL.TotalReceived, 0) AS RECIVEDQTY,
    COALESCE(ISSUE_TOTAL.TotalIssued, 0) AS ISSUEQTY
FROM Item I
-- 左连接聚合后的采购总数据
LEFT JOIN (
    SELECT
        ItemID,
        SUM(ReceivedQTY) AS TotalReceived,
        IssueStockRate
    FROM purchasebilldetails
    GROUP BY ItemID, IssueStockRate
) PUBD_TOTAL ON I.Item_Id = PUBD_TOTAL.ItemID
-- 左连接聚合后的出库总数据
LEFT JOIN (
    SELECT
        ItemID,
        SUM(QTYIssue) AS TotalIssued
    FROM issuestockdetails
    GROUP BY ItemID
) ISSUE_TOTAL ON I.Item_Id = ISSUE_TOTAL.ItemID
WHERE I.ComId = 8
-- 过滤可用库存大于0的商品
HAVING (I.QTY + COALESCE(PUBD_TOTAL.TotalReceived, 0) - COALESCE(ISSUE_TOTAL.TotalIssued, 0)) > 0
GROUP BY I.Item_Id, I.Item_Name, I.Specification, I.Item_HSN, I.unit, I.Serial_number_applied, I.QTY, PUBD_TOTAL.IssueStockRate, PUBD_TOTAL.TotalReceived, ISSUE_TOTAL.TotalIssued;

关键说明 #

• 子查询PUBD_TOTAL和ISSUE_TOTAL先完成各自的聚合计算，确保每个商品只有一条汇总记录，从根源避免数据重复
• 使用LEFT JOIN而非INNER JOIN，可以保留那些没有采购或出库记录的商品（此时对应汇总值会被COALESCE转为0）
• 用COALESCE处理NULL值，防止因为无采购/出库记录导致计算结果为NULL

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