SQL Server物料收发累计成本计算的正确实现方法问询

你的核心问题在于：出库行的单价和累计成本依赖上一行的计算结果，属于逐行递推的迭代逻辑，而普通窗口函数（如SUM OVER、LAG）是基于整个数据集的静态聚合，无法实现这种依赖前序行动态结果的计算，必须用递归CTE来实现逐行迭代。

实现思路 #

1. 按StoreNo（仓库号）和MatCode（物料编码）分组，按交易顺序（RowNo）逐行处理
2. 锚点成员：初始化每组的第一笔交易的累计成本
3. 递归成员：基于上一行的累计成本（CCost）和库存余额（Bal），计算当前行的单价、交易成本和累计成本：
   • 入库行（TransType='RCV'）：直接使用MRV表的单价和交易成本，累计成本累加本次交易成本
   • 出库行：用上一行的累计成本除以库存余额得到单价，再计算本次出库成本，累计成本减去本次出库成本

完整SQL代码 #

WITH RecursiveMatTrans AS (
    -- 锚点：获取每组物料的第一笔交易，初始化累计成本
    SELECT 
        RowNo,
        StoreNo,
        MatCode,
        TransDate,
        TransType,
        TransNo,
        Qty,
        Bal,
        UPrice,
        TransCost,
        CASE WHEN TransType = 'RCV' THEN TransCost ELSE 0 END AS CCost
    FROM MatTrans mt
    WHERE RowNo = (SELECT MIN(RowNo) FROM MatTrans WHERE StoreNo = mt.StoreNo AND MatCode = mt.MatCode)

    UNION ALL

    -- 递归：逐行计算后续交易，依赖上一行的结果
    SELECT 
        mt.RowNo,
        mt.StoreNo,
        mt.MatCode,
        mt.TransDate,
        mt.TransType,
        mt.TransNo,
        mt.Qty,
        mt.Bal,
        -- 计算当前行单价
        CASE 
            WHEN mt.TransType = 'RCV' THEN mt.UPrice
            -- 处理库存为0的异常情况，避免除数为0
            ELSE CASE WHEN rmt.Bal = 0 THEN 0 ELSE rmt.CCost / rmt.Bal END
        END AS UPrice,
        -- 计算当前行交易成本
        CASE 
            WHEN mt.TransType = 'RCV' THEN mt.TransCost
            ELSE CASE WHEN rmt.Bal = 0 THEN 0 ELSE (rmt.CCost / rmt.Bal) * mt.Qty END
        END AS TransCost,
        -- 计算当前行累计成本
        CASE 
            WHEN mt.TransType = 'RCV' THEN rmt.CCost + mt.TransCost
            ELSE rmt.CCost - CASE WHEN rmt.Bal = 0 THEN 0 ELSE (rmt.CCost / rmt.Bal) * mt.Qty END
        END AS CCost
    FROM MatTrans mt
    INNER JOIN RecursiveMatTrans rmt 
        ON mt.StoreNo = rmt.StoreNo 
        AND mt.MatCode = rmt.MatCode 
        AND mt.RowNo = rmt.RowNo + 1
)
-- 输出最终结果，可根据需求调整小数位数
SELECT 
    RowNo,
    StoreNo,
    MatCode,
    TransDate,
    TransType,
    TransNo,
    Qty,
    Bal,
    ROUND(UPrice, 4) AS UPrice,
    ROUND(TransCost, 2) AS TransCost,
    ROUND(CCost, 2) AS CCost
FROM RecursiveMatTrans
ORDER BY StoreNo, MatCode, RowNo;

关键说明 #

• 若RowNo不是连续递增的，需先为每组物料生成连续排序号，替换递归中的RowNo关联逻辑：

  -- 先生成连续行号
  WITH RankedMatTrans AS (
      SELECT *,
          ROW_NUMBER() OVER (PARTITION BY StoreNo, MatCode ORDER BY TransDate, RowNo) AS SeqNo
      FROM MatTrans
  )
  -- 后续递归用SeqNo关联，锚点取SeqNo=1，递归条件为mt.SeqNo = rmt.SeqNo +1
  

• 代码中处理了库存为0的异常情况，实际业务中应避免无库存出库，可根据需求调整异常逻辑
• 入库行的TransCost直接复用MatTrans中的值（假设MRV表已正确计算），若需重新计算可替换为mt.Qty * mt.UPrice

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