SQL Server 2008中按先进先出(FIFO)规则实现库存出库查询

问题背景 #

现有库存批次数据表如下：

存在两个业务场景：

1. 从最早批次提取85件ITEM Number为0098的物料，期望输出：

2. 下一个订单提取127件ITEM Number为0098的物料，期望输出：

以下是SQL Server 2008中实现**先进先出(FIFO)**出库清单的解决方案：

方案一：实时更新库存表（业务常用场景） #

该方案会直接扣减库存表中的可用数量，适合实际出库业务流程。

处理第一个订单（AA0001，提取85件）

-- 生成出库记录
INSERT INTO OutboundRecords (OrderNumber, BatchNumber, ItemNumber, Qty)
SELECT 'AA0001', BatchNumber, '0098', 85
FROM InventoryBatches
WHERE ItemNumber = '0098'
ORDER BY BatchNumber ASC -- 按批次号升序，默认批次号越小入库越早
OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY;

-- 更新库存表扣减数量
UPDATE InventoryBatches
SET Qty = Qty - 85
WHERE BatchNumber = '00980001' AND ItemNumber = '0098';

处理第二个订单（AA0002，提取127件）

-- 计算可用批次的累积库存，确定需要动用的批次
WITH AvailableBatches AS (
    SELECT 
        BatchNumber,
        ItemNumber,
        Qty,
        -- 计算当前及之前批次的累积可用库存
        (SELECT SUM(Qty) FROM InventoryBatches b2 
         WHERE b2.ItemNumber = b1.ItemNumber AND b2.BatchNumber <= b1.BatchNumber) AS CumulativeQty
    FROM InventoryBatches b1
    WHERE ItemNumber = '0098' AND Qty > 0
    ORDER BY BatchNumber ASC
)
-- 生成本次出库记录
INSERT INTO OutboundRecords (OrderNumber, BatchNumber, ItemNumber, Qty)
SELECT 
    'AA0002',
    BatchNumber,
    ItemNumber,
    -- 计算每个批次的出库量：如果累积库存小于需求则全出，否则只出剩余需求
    CASE 
        WHEN CumulativeQty <= 127 THEN Qty
        ELSE 127 - (CumulativeQty - Qty)
    END AS OutQty
FROM AvailableBatches
WHERE CumulativeQty - Qty < 127;

-- 更新库存表扣减对应批次数量
WITH Outbound AS (
    SELECT BatchNumber, Qty AS OutQty FROM OutboundRecords WHERE OrderNumber = 'AA0002'
)
UPDATE ib
SET Qty = ib.Qty - ob.OutQty
FROM InventoryBatches ib
JOIN Outbound ob ON ib.BatchNumber = ob.BatchNumber;

方案二：保留原始库存，通过计算已出库量实现FIFO #

该方案不修改原始库存表，适合查询历史出库清单或模拟出库场景，需依赖OutboundRecords表存储所有出库记录。

通用查询模板（支持任意订单需求）

-- 定义参数：订单号、物料编号、需求数量
DECLARE @OrderNumber VARCHAR(20) = 'AA0002';
DECLARE @ItemNumber VARCHAR(20) = '0098';
DECLARE @ReqQty INT = 127;

-- 计算每个批次的已出库量、可用量及累积可用量
WITH BatchUsage AS (
    SELECT 
        ib.BatchNumber,
        ib.ItemNumber,
        ib.Qty AS OriginalQty,
        ISNULL(SUM(ob.Qty), 0) AS UsedQty,
        ib.Qty - ISNULL(SUM(ob.Qty), 0) AS AvailableQty,
        -- 计算当前及之前批次的累积可用库存
        (SELECT SUM(ib2.Qty - ISNULL(SUM(ob2.Qty), 0)) 
         FROM InventoryBatches ib2
         LEFT JOIN OutboundRecords ob2 ON ib2.BatchNumber = ob2.BatchNumber AND ib2.ItemNumber = ob2.ItemNumber
         WHERE ib2.ItemNumber = ib.ItemNumber AND ib2.BatchNumber <= ib.BatchNumber) AS CumulativeAvailable
    FROM InventoryBatches ib
    LEFT JOIN OutboundRecords ob ON ib.BatchNumber = ob.BatchNumber AND ib.ItemNumber = ob.ItemNumber
    WHERE ib.ItemNumber = @ItemNumber
    GROUP BY ib.BatchNumber, ib.ItemNumber, ib.Qty
    HAVING ib.Qty - ISNULL(SUM(ob.Qty), 0) > 0
    ORDER BY ib.BatchNumber ASC
),
-- 计算本次出库的批次和数量
OutboundCalculation AS (
    SELECT 
        @OrderNumber AS OrderNumber,
        BatchNumber,
        ItemNumber,
        CASE 
            WHEN CumulativeAvailable <= @ReqQty THEN AvailableQty
            ELSE @ReqQty - (CumulativeAvailable - AvailableQty)
        END AS OutQty
    FROM BatchUsage
    WHERE CumulativeAvailable - AvailableQty < @ReqQty
)
-- 可选：保存出库记录到表
INSERT INTO OutboundRecords (OrderNumber, BatchNumber, ItemNumber, Qty)
SELECT OrderNumber, BatchNumber, ItemNumber, OutQty FROM OutboundCalculation;

-- 查询本次出库清单
SELECT * FROM OutboundCalculation;

关键说明 #

1. 批次排序逻辑：默认按BatchNumber升序实现FIFO，若批次号不代表入库时间，需替换为InboundDate（入库日期）字段排序。
2. SQL Server 2008兼容：由于2008版本不支持SUM() OVER(ORDER BY ...)窗口函数，采用自连接子查询计算累积库存，保证兼容性。
3. 边界处理：自动处理跨批次出库的情况，最后一个批次仅提取满足剩余需求的数量。

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