递归CTE计算库存均价适配多Item问题求助

问题背景 #

你现在遇到的情况很典型：单Item（比如2000045）时，递归CTE能正常从首个ReceiptNo开始计算初始均价，再递推后续的均价，但当表中有多个不同的Item No_及其对应的ReceiptNo时，查询就失效了——这是因为原来的CTE硬编码了特定Item，而且递归关联时没按Item分组，导致不同Item的计算逻辑串在了一起。

问题根源分析 #

看你原来的递归CTE代码，不管是锚点成员还是递归成员，都固定指定了A.[Item No_]='2000045'，而且递归关联时只匹配了X.ReceiptNo = X1.ReceiptNo +1，没有关联Item No_，所以多Item时，不同Item的ReceiptNo会互相干扰，计算结果自然不对。

解决方案一：修改递归CTE支持多Item #

我们可以调整CTE，让它对每个Item No_独立进行递归计算，不需要硬编码Item值，这也是性能最优的方案：

;WITH Testcte AS (
    -- 锚点成员：获取每个Item的第一个ReceiptNo（ReceiptNo=1）的初始均价
    SELECT 
        A.*,
        ((A.PurchaseQty * A.IntakeSellingPrice) + (A.InventoryBalance * ISNULL(A2.IntakeSellingPrice, A.IntakeSellingPrice))) / A.NewBalance AS [RunningAVG]
    FROM AKTest A
    LEFT JOIN AKTest A2 
        ON A.[Item No_] = A2.[Item No_] 
        AND A.ReceiptNo = A2.ReceiptNo + 1
    WHERE A.ReceiptNo = 1  -- 每个Item的起始记录
    
    UNION ALL
    
    -- 递归成员：基于同Item的上一个ReceiptNo的均价计算当前均价
    SELECT 
        X.*,
        ((X.PurchaseQty * X.IntakeSellingPrice) + (X.InventoryBalance * X1.RunningAVG)) / X.NewBalance AS [RunningAVG]
    FROM AKTest X
    JOIN Testcte X1 
        ON X.[Item No_] = X1.[Item No_]  -- 关键：按Item分组递归
        AND X.ReceiptNo = X1.ReceiptNo + 1
)
SELECT * FROM Testcte
ORDER BY [Item No_], ReceiptNo;  -- 按Item和ReceiptNo排序，方便查看

这个修改后的CTE会自动对每个Item No_单独处理，从各自的ReceiptNo=1开始递推，完全支持多Item场景。

解决方案二：使用游标遍历每个Item计算 #

如果因为某些特殊需求你需要用游标实现，这里是具体的代码示例：

-- 声明变量
DECLARE @ItemNo NVARCHAR(20);
DECLARE @ResultTable TABLE (
    IntakeSellingPrice DECIMAL(38,20),
    IntakeSellingAmount DECIMAL(38,6),
    [Item No_] NVARCHAR(20),
    [Posting Date] DATETIME,
    PurchaseQty DECIMAL(38,20),
    ReceiptNo BIGINT,
    InventoryBalance DECIMAL(38,20),
    NewBalance DECIMAL(38,20),
    RunningAVG DECIMAL(38,20)
);

-- 声明游标，遍历所有不同的Item No_
DECLARE ItemCursor CURSOR FOR
SELECT DISTINCT [Item No_] FROM AKTest;

-- 打开游标
OPEN ItemCursor;
FETCH NEXT FROM ItemCursor INTO @ItemNo;

-- 循环处理每个Item
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 对当前Item执行递归CTE计算
    ;WITH Testcte AS (
        SELECT 
            A.*,
            ((A.PurchaseQty * A.IntakeSellingPrice) + (A.InventoryBalance * ISNULL(A2.IntakeSellingPrice, A.IntakeSellingPrice))) / A.NewBalance AS [RunningAVG]
        FROM AKTest A
        LEFT JOIN AKTest A2 
            ON A.[Item No_] = A2.[Item No_] 
            AND A.ReceiptNo = A2.ReceiptNo + 1
        WHERE A.ReceiptNo = 1 
          AND A.[Item No_] = @ItemNo
        
        UNION ALL
        
        SELECT 
            X.*,
            ((X.PurchaseQty * X.IntakeSellingPrice) + (X.InventoryBalance * X1.RunningAVG)) / X.NewBalance AS [RunningAVG]
        FROM AKTest X
        JOIN Testcte X1 
            ON X.[Item No_] = X1.[Item No_] 
            AND X.ReceiptNo = X1.ReceiptNo + 1
          AND X.[Item No_] = @ItemNo
    )
    -- 将当前Item的计算结果插入临时表
    INSERT INTO @ResultTable
    SELECT * FROM Testcte;

    -- 获取下一个Item
    FETCH NEXT FROM ItemCursor INTO @ItemNo;
END

-- 关闭并释放游标
CLOSE ItemCursor;
DEALLOCATE ItemCursor;

-- 输出所有结果
SELECT * FROM @ResultTable
ORDER BY [Item No_], ReceiptNo;

游标方案的思路是逐个取出每个Item No_，然后对单个Item执行原来的递归CTE计算，最后把所有结果汇总输出。不过要注意，游标在数据量较大时性能不如修改后的递归CTE，所以优先推荐第一种方案。

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