SQL Server按组件分组逐行计算预计净库存及缺料标识咨询

核心通过SQL Server自带的带分区排序的窗口聚合函数即可实现需求，无需额外递归或者行级变量引用，完整实现代码如下：

WITH base_data AS (
    SELECT 
        A.[MFNO - Manufacturing order number] AS Job_No,
        A.[MTNO - Component number] AS Comp,
        A.[RDAT - Reservation date] AS [Req dt],
        A.[REQT - Reserved quantity] AS ReQ,
        ISNULL(C.[STQT - On-hand balance approved], 0) AS AvSt, -- 无匹配库存数据时默认可用库存为0，可根据业务规则调整
        ISNULL(C.[REQT - Reserved quantity], 0) AS ResQ,        -- 无匹配预留数据时默认预留量为0，可根据业务规则调整
        A.[RGDT - Entry date]
    FROM [JOB ORDERS] A    
    LEFT JOIN [WAREHOUSE STOCK] C 
        ON A.[MTNO - Component number] = C.[ITNO - Item number] 
        AND A.[WHLO - Warehouse] = C.[WHLO - Warehouse]
    WHERE
        A.[WMST - Material status] <= '44' 
        AND A.[SCHN - Schedule number] <> 0
        AND A.[RGDT - Entry date] > 20210101
        AND A.[REQT - Reserved quantity] > 0
)
SELECT 
    Job_No,
    Comp,
    [Req dt],
    ReQ,
    AvSt,
    ResQ,
    -- 按规则计算净库存
    (AvSt - ResQ) - SUM(ReQ) OVER (
        PARTITION BY Comp 
        ORDER BY [Req dt] 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS NSt,
    -- 计算缺料标识
    CASE 
        WHEN (AvSt - ResQ) - SUM(ReQ) OVER (
            PARTITION BY Comp 
            ORDER BY [Req dt] 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) >= 0 THEN 'No'
        ELSE 'Yes'
    END AS LSt,
    [RGDT - Entry date]
FROM base_data
ORDER BY Comp, [Req dt]

实现逻辑说明 #

• 首先将原有基础查询封装为CTE base_data，统一处理空值问题避免计算报错
• 核心的累计计算通过SUM() OVER()窗口函数实现：
  • PARTITION BY Comp 按组件编码分区，切换到新组件时会自动重置计算逻辑
  • ORDER BY [Req dt] 按需求日期排序，保证计算顺序和业务规则要求一致
  • 求和范围指定为当前分区第一行到当前行，得到截止当前行的组件累计需求总量
  • 用组件初始可消耗库存（AvSt - ResQ）减去累计需求，正好匹配要求的净库存规则：首行扣减第一笔需求，后续行自动继承上一行剩余库存扣减当前需求
• 缺料标识直接基于计算得到的净库存判断即可

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