SQL Server按不同标志计算累计值/条件运行总计

首先，我先明确下你的需求核心：要为每个商品（Art.No）计算每行交易对应的库存累计值，需要区分库存盘点（Flag=V）前后的逻辑——盘点前的历史只统计进货（I）和销售（U）的变动，盘点后则以盘点的库存数为起点，继续累加后续的进销变动；没有盘点记录的商品就全程统计进销变动。

原交易表数据（假设表名为Transactions） #

ID  Flag Art.No  Amount
1   U    A1000   -100
2   U    B2000   -5
3   V    B2000   900
4   U    B2000   -10
5   I    B2000   50
6   U    B2000   -20
7   U    A1000   -50
8   I    A1000   1000
9   V    A1000   3600
10  U    A1000   -500
11  U    A1000   -100
12  U    A1000   -2000
13  I    A1000   2000
14  U    A1000   -1000
15  I    C3000   10000
16  U    C3000   -4000
17  U    B2000   -5
18  U    B2000   -5
19  I    B2000   40
20  V    B2000   200
21  U    A1000   -500
22  U    B2000   -50
23  U    C3000   -1000

解决方案：分步CTE实现逻辑 #

我用多个CTE来拆解逻辑，让代码更清晰易懂：

WITH LatestV AS (
    -- 第一步：找到每个商品的最新盘点（V）交易的ID和库存值
    SELECT 
        [Art.No],
        MAX(ID) AS LatestV_ID,
        MAX(CASE WHEN Flag = 'V' THEN Amount END) AS LatestV_Amount
    FROM Transactions
    WHERE Flag = 'V'
    GROUP BY [Art.No]
),
TransactionGroups AS (
    -- 第二步：给每行交易标记所属的逻辑组
    SELECT 
        t.ID,
        t.Flag,
        t.[Art.No],
        t.Amount,
        lv.LatestV_ID,
        lv.LatestV_Amount,
        CASE 
            WHEN lv.LatestV_ID IS NULL THEN 'NoV'          -- 无盘点记录的商品
            WHEN t.ID >= lv.LatestV_ID THEN 'PostV'       -- 盘点及之后的交易
            ELSE 'PreV'                                   -- 盘点之前的历史交易
        END AS GroupType
    FROM Transactions t
    LEFT JOIN LatestV lv ON t.[Art.No] = lv.[Art.No]
),
PreV_Cumulative AS (
    -- 第三步：计算盘点前的历史累计（仅统计U/I的变动，忽略V）
    SELECT 
        ID,
        [Art.No],
        SUM(CASE WHEN Flag IN ('U','I') THEN Amount ELSE 0 END) 
            OVER (PARTITION BY [Art.No] ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Inventory_Cumulative
    FROM TransactionGroups
    WHERE GroupType = 'PreV'
),
PostV_Cumulative AS (
    -- 第四步：计算盘点后的累计（以盘点值为起点，累加后续U/I变动）
    SELECT 
        tg.ID,
        tg.[Art.No],
        -- 注意：要减去盘点本身的Amount，因为SUM会包含它，但盘点是初始值不是变动值
        tg.LatestV_Amount + 
            SUM(CASE WHEN Flag IN ('U','I') THEN Amount ELSE 0 END) 
                OVER (PARTITION BY tg.[Art.No] ORDER BY tg.ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
            - CASE WHEN tg.Flag = 'V' THEN tg.Amount ELSE 0 END AS Inventory_Cumulative
    FROM TransactionGroups tg
    WHERE GroupType = 'PostV'
),
NoV_Cumulative AS (
    -- 第五步：计算无盘点记录商品的全程累计（仅统计U/I）
    SELECT 
        ID,
        [Art.No],
        SUM(CASE WHEN Flag IN ('U','I') THEN Amount ELSE 0 END) 
            OVER (PARTITION BY [Art.No] ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Inventory_Cumulative
    FROM TransactionGroups
    WHERE GroupType = 'NoV'
)
-- 最后合并所有组的结果，按商品和交易ID排序
SELECT 
    t.ID,
    t.Flag,
    t.[Art.No],
    t.Amount,
    COALESCE(pv.Inventory_Cumulative, psv.Inventory_Cumulative, nov.Inventory_Cumulative) AS Inventory_Cumulative
FROM Transactions t
LEFT JOIN PreV_Cumulative pv ON t.ID = pv.ID AND t.[Art.No] = pv.[Art.No]
LEFT JOIN PostV_Cumulative psv ON t.ID = psv.ID AND t.[Art.No] = psv.[Art.No]
LEFT JOIN NoV_Cumulative nov ON t.ID = nov.ID AND t.[Art.No] = nov.[Art.No]
ORDER BY t.[Art.No], t.ID;

逻辑解释 #

1. LatestV：精准定位每个商品的最后一次盘点交易，这是后续计算的分界点。
2. TransactionGroups：给每行打上标签，让我们能分别处理不同阶段的交易。
3. PreV_Cumulative：对于盘点前的历史，只累加进货和销售的变动，完全忽略盘点记录（因为需求要求盘点前的历史不计入盘点值）。
4. PostV_Cumulative：以最后一次盘点的库存数为基准，往后累加每一笔进销变动。这里特意减去了盘点本身的数值，因为窗口函数的SUM会把盘点的Amount算进去，但盘点是初始值，不是变动项。
5. NoV_Cumulative：没有盘点记录的商品，直接全程累加进销变动即可。
6. 最后用COALESCE合并三个组的结果，确保每行都能拿到对应的累计值，再按商品和交易时间（ID顺序）排序。

执行这段代码后，得到的结果会完全匹配你给出的示例：比如A1000在ID9（盘点）后的累计从3600开始，B2000在ID20（最新盘点）后的累计从200开始，C3000全程累加进销变动。

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