如何使用MySQL内置函数计算水果的当前可用库存？

我有两张数据表，分别记录采购和出库信息，需要计算每种水果的当前可用库存。

数据表结构与数据 #

采购表 tblPurchaseDetails

出库表 tblStockoutdetails

期望结果 #

我希望得到每种水果的当前可用库存，格式如下：

我尝试过的SQL #

select purchasedetails.Particular, sum(purchasedetails.qty)-sum(stockoutdetails.Qty) 
from purchasedetails left join stockoutdetails on purchasedetails.particular=stockoutdetails.particular 
GROUP by purchasedetails.particular

但这个查询没有得到正确结果，请问如何用MySQL内置函数实现需求？

解决方案 #

你之前的查询之所以没得到正确结果，是因为直接关联两张表后就求和，会产生笛卡尔积——比如Apple有2条采购记录、2条出库记录，关联后会生成4条数据，求和时采购量和出库量都会被重复计算，结果自然不准。

给你两种实用的解决思路，都能准确算出库存：

方法一：先分别统计采购/出库总量，再关联计算

先把两张表各自按水果分组，算出总采购量和总出库量，再关联起来做减法。用COALESCE函数处理那些只有采购没有出库（比如Papaya）的情况，避免NULL值导致计算错误：

SELECT 
    COALESCE(p.ParticularID, s.ParticularID) AS ParticularID,
    COALESCE(p.Particular, s.Particular) AS Particular,
    COALESCE(p.TotalPurchase, 0) - COALESCE(s.TotalStockout, 0) AS Qty
FROM (
    -- 统计每种水果的总采购量
    SELECT ParticularID, Particular, SUM(Qty) AS TotalPurchase
    FROM tblPurchaseDetails
    GROUP BY ParticularID, Particular
) p
LEFT JOIN (
    -- 统计每种水果的总出库量
    SELECT ParticularID, Particular, SUM(Qty) AS TotalStockout
    FROM tblStockoutdetails
    GROUP BY ParticularID, Particular
) s ON p.ParticularID = s.ParticularID
ORDER BY ParticularID;

方法二：合并数据后统一求和（更简洁）

把采购数据保留正数，出库数据转为负数，用UNION ALL合并后直接按水果分组求和，这样一步到位：

SELECT 
    ParticularID,
    Particular,
    SUM(Qty) AS Qty
FROM (
    -- 采购量为正
    SELECT ParticularID, Particular, Qty FROM tblPurchaseDetails
    UNION ALL
    -- 出库量为负
    SELECT ParticularID, Particular, -Qty FROM tblStockoutdetails
) combined_data
GROUP BY ParticularID, Particular
ORDER BY ParticularID;

小提示 #

• COALESCE函数的作用是：如果第一个参数是NULL，就返回第二个参数。比如Papaya没有出库记录，出库量是NULL，用COALESCE(s.TotalStockout, 0)就能把它转成0，确保10 - 0 = 10的正确结果。
• 两种方法都能得到你想要的结果，第二种写法更简洁，适合数据量不大的场景；第一种逻辑更直观，便于后续扩展（比如要单独查看采购/出库总量时）。

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