SQL Server中使用CTE实现带盘点修正的当前库存计算咨询

针对你提出的库存计算需求，以下是基于递归CTE的实现方案，替代循环更新以提升大数据集下的处理效率：

假设数据表结构 #

假设你的库存表名为Inventory，包含字段：RowId（行号，有序）、Entry（入库量）、Exit（出库量）、StockTaking（盘点量，0表示无盘点修正）。

基础版递归CTE实现（RowId连续递增） #

WITH InventoryCTE AS (
    -- 锚点成员：初始行（RowId=1）
    SELECT 
        RowId,
        Entry,
        Exit,
        StockTaking,
        CAST(StockTaking AS DECIMAL(18,2)) AS CurrentStock
    FROM Inventory
    WHERE RowId = 1

    UNION ALL

    -- 递归成员：逐行计算后续库存
    SELECT 
        t.RowId,
        t.Entry,
        t.Exit,
        t.StockTaking,
        CASE 
            -- 规则3：存在盘点值时直接用该值作为当前库存
            WHEN t.StockTaking <> 0 THEN CAST(t.StockTaking AS DECIMAL(18,2))
            -- 规则2：无盘点时用上一行库存+入库-出库计算
            ELSE cte.CurrentStock + t.Entry - t.Exit
        END AS CurrentStock
    FROM Inventory t
    INNER JOIN InventoryCTE cte ON t.RowId = cte.RowId + 1
)
SELECT * FROM InventoryCTE
ORDER BY RowId;

适配非连续RowId的版本 #

如果RowId存在缺失、不连续的情况，先通过ROW_NUMBER()生成连续序号后再计算：

WITH OrderedInventory AS (
    -- 生成连续排序的序号
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY RowId) AS SeqId
    FROM Inventory
),
InventoryCTE AS (
    -- 锚点成员：初始行（SeqId=1）
    SELECT 
        RowId,
        Entry,
        Exit,
        StockTaking,
        SeqId,
        CAST(StockTaking AS DECIMAL(18,2)) AS CurrentStock
    FROM OrderedInventory
    WHERE SeqId = 1

    UNION ALL

    -- 递归成员：基于连续序号逐行计算
    SELECT 
        t.RowId,
        t.Entry,
        t.Exit,
        t.StockTaking,
        t.SeqId,
        CASE 
            WHEN t.StockTaking <> 0 THEN CAST(t.StockTaking AS DECIMAL(18,2))
            ELSE cte.CurrentStock + t.Entry - t.Exit
        END AS CurrentStock
    FROM OrderedInventory t
    INNER JOIN InventoryCTE cte ON t.SeqId = cte.SeqId + 1
)
-- 输出结果时剔除临时序号SeqId
SELECT RowId, Entry, Exit, StockTaking, CurrentStock FROM InventoryCTE
ORDER BY SeqId;

方案说明 #

• 递归CTE采用集合式操作，比循环更新的逐行处理效率更高，适合大数据场景。
• 锚点成员直接处理初始行，符合规则1的要求。
• 递归成员通过CASE分支同时处理正常计算和盘点修正两种场景，严格遵循你定义的3条规则：遇到盘点值时直接覆盖当前库存，后续行自动基于修正后的值继续计算。

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