SQL迭代计算库存问题求助：已尝试Cross Apply未获预期结果

嘿，我来帮你搞定这个库存累计计算的问题！根据你的需求，我们需要基于operation类型，从初始值0开始，对qty进行加减得到每行的newstock。之前用CROSS APPLY没得到预期结果，大概率是写法的问题，下面给你几种靠谱的解决方案：

方法一：使用SUM窗口函数（推荐，性能最优） #

这是处理这类累计计算最简洁高效的方式，利用窗口函数直接对正负化后的qty做累计求和：

SELECT 
    id,
    qty,
    price,
    operation,
    -- 把buy的qty设为正，sale设为负，然后按id排序累计求和
    SUM(CASE operation 
            WHEN 'buy' THEN qty 
            WHEN 'sale' THEN -qty 
            ELSE 0 
        END) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS newstock
FROM your_table_name
ORDER BY id;

代码解释： #

• CASE语句将sale操作的qty转为负值，buy保持正值；
• SUM() OVER (...)窗口函数按id排序，从第一行到当前行累计求和，初始值就是0（第一行之前无数据，所以直接加当前行的正负qty）；
• 这种方式不需要关联子查询，执行效率远高于CROSS APPLY，适合大数据量场景。

方法二：修正后的CROSS APPLY写法 #

如果你坚持要用CROSS APPLY，可以用关联子查询的方式累计计算，注意要按id过滤出当前行及之前的所有记录：

SELECT 
    t.id,
    t.qty,
    t.price,
    t.operation,
    -- 对当前行及之前的所有记录计算累计值
    SUM(CASE WHEN t2.operation = 'buy' THEN t2.qty ELSE -t2.qty END) AS newstock
FROM your_table_name t
CROSS APPLY (
    SELECT * 
    FROM your_table_name t2 
    WHERE t2.id <= t.id -- 只取当前行及之前的数据
) t2
GROUP BY t.id, t.qty, t.price, t.operation
ORDER BY t.id;

方法三：递归CTE（适合理解逻辑） #

如果想要更直观地看到每一步的计算过程，可以用递归CTE实现，尤其适合id不连续的场景：

-- 先给每行生成连续行号（如果id本身连续可以跳过这一步）
WITH NumberedRows AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM your_table_name
),
-- 递归计算库存
RecursiveStock AS (
    -- 初始行：第一行的库存值
    SELECT 
        id,
        qty,
        price,
        operation,
        rn,
        CASE operation WHEN 'buy' THEN qty ELSE -qty END AS newstock
    FROM NumberedRows
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归遍历后续行，基于上一行的库存计算当前行
    SELECT 
        t.id,
        t.qty,
        t.price,
        t.operation,
        t.rn,
        CASE t.operation 
            WHEN 'buy' THEN r.newstock + t.qty 
            ELSE r.newstock - t.qty 
        END AS newstock
    FROM NumberedRows t
    JOIN RecursiveStock r ON t.rn = r.rn + 1
)
-- 输出最终结果
SELECT id, qty, price, operation, newstock FROM RecursiveStock ORDER BY id;

注意事项： #

• 确保id是按操作顺序排序的，否则累计结果会出错；
• 如果你的表中有其他排序依据（比如操作时间），可以把ORDER BY id改成对应的字段（比如ORDER BY operation_time）。

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