SQL Server库存数量计算问题：如何准确获取现有库存

问题背景 #

开发简易库存系统时，需要从三个数据表计算现有库存，目标公式是sum(receiving_stock) + sum(returning_stock) - sum(outgoing_stock)。但当outgoing_stock表无数据时，查询返回的Prod_id全部为Null，无法得到准确的现有库存数据。

数据表内容 #

receiving_stock表 #

returning_stock表 #

outgoing_stock表 #

暂无数据

查询结果对比 #

实际结果 #

期望结果 #

原查询语句 #

Select prod_id, isnull(qty,0)-isnull(sold,0) on-hand
Select prod_id, sum(qty) qty
(
select prod_id,qty
From receiving_stock
Union all
select prod_id,qty
From returning_stock
) Za
Group by prod_id
) Zb
Left join
(
From
Select prod_id, sum(qty) sold
From outgoing_stock
Group by prod_id
) zc
) zd
On
Zb.prod_id=zd.prod_id

问题分析与修正 #

原查询存在语法错误（多余关键字、括号不匹配），且左连接后未正确保留入库表的prod_id，导致出库表无数据时prod_id变为Null。修正后的查询如下：

SELECT 
    zb.prod_id,
    ISNULL(zb.total_in, 0) - ISNULL(zc.total_out, 0) AS on_hand
FROM (
    -- 合并入库和退货数据，计算总入库量
    SELECT prod_id, SUM(qty) AS total_in
    FROM (
        SELECT prod_id, qty FROM receiving_stock
        UNION ALL
        SELECT prod_id, qty FROM returning_stock
    ) za
    GROUP BY prod_id
) zb
-- 左连接出库数据，确保无出库记录时仍保留产品ID
LEFT JOIN (
    SELECT prod_id, SUM(qty) AS total_out
    FROM outgoing_stock
    GROUP BY prod_id
) zc ON zb.prod_id = zc.prod_id

关键说明 #

• 用UNION ALL合并入库和退货数据，避免去重（符合库存累加逻辑）
• 左连接出库汇总表，确保即使出库表为空，也能保留所有入库产品的ID
• 用ISNULL处理Null值，当出库量为Null时按0计算，保证库存数值准确

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