SQL Server按效期优先提取物料24共150件库存的查询实现

核心需求 #

• 出库对象：Itemcode=24的物料，总出库需求150件
• 分配规则：严格按照先到期先出逻辑分配拣货量，效期越早的批次优先占用，直到凑齐总需求数量
• 库存表结构：表共4个字段
  • id：批次唯一ID
  • Itemcode：物料编码
  • ExpireDate：批次到期日期
  • Qty：批次当前可用库存数量
• 样例库存数据：
  • id=1，Itemcode=24，ExpireDate=2023-5，Qty=25
  • id=2，Itemcode=24，ExpireDate=2023-8，Qty=50
  • id=3，Itemcode=24，ExpireDate=2023-10，Qty=100
  • id=4，Itemcode=24，ExpireDate=2024-1，Qty=100
• 预期分配结果：全额提取id=1批次25件、id=2批次50件，从id=3批次提取75件，合计150件，id=4批次不参与本次分配。

实现逻辑 #

通过窗口函数按效期升序计算累计库存，逐批次判断拣货量：

1. 筛选指定物料的所有有效库存，按到期日期从早到晚排序，同效期按批次id升序排序
2. 计算每个批次之前所有排序靠前批次的库存累计值
3. 对比累计值和总需求，判断当前批次是全额提取、部分提取还是不提取：
   • 前置累计库存已经满足总需求：当前批次拣货量为0
   • 前置累计库存+当前批次库存 ≤ 总需求：当前批次全额拣货
   • 其他情况：当前批次拣货量 = 总需求 - 前置累计库存

实现代码 #

注意：代码中Inventory为示例库存表名，请替换为实际业务中的库存表名即可使用。

-- 配置业务参数：目标物料编码、总出库需求
DECLARE @TargetItemcode INT = 24;
DECLARE @TotalDemand INT = 150;

WITH StockSortedByExpire AS (
    SELECT
        id,
        Itemcode,
        ExpireDate,
        Qty,
        -- 计算排在当前批次之前的所有效期更早批次的库存总和
        SUM(Qty) OVER (
            ORDER BY ExpireDate ASC, id ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS PrevStockSum
    FROM Inventory
    WHERE Itemcode = @TargetItemcode
)
SELECT
    id,
    Itemcode,
    ExpireDate,
    Qty AS BatchStock,
    -- 计算当前批次实际需要拣货的数量
    CASE
        WHEN PrevStockSum >= @TotalDemand THEN 0
        WHEN PrevStockSum + Qty <= @TotalDemand THEN Qty
        ELSE @TotalDemand - PrevStockSum
    END AS PickQty
FROM StockSortedByExpire
-- 过滤掉无拣货任务的批次
WHERE
    CASE
        WHEN PrevStockSum >= @TotalDemand THEN 0
        WHEN PrevStockSum + Qty <= @TotalDemand THEN Qty
        ELSE @TotalDemand - PrevStockSum
    END > 0
ORDER BY ExpireDate ASC, id ASC;

运行结果 #

针对给出的样例数据，上述代码执行后返回结果完全匹配预期分配逻辑：

补充说明：如果对应物料的总库存小于出库需求，代码会自动返回所有可用库存的拣货量，不会抛出异常，可根据业务需要额外增加库存不足的校验逻辑。

内容的提问来源于stack exchange，提问作者Mr. Bashar Basim