如何在同一张表中累加行值更新Stock on hand列

需求说明 #

需要将每行及之前所有行的Qty值累加，结果填入「Stock on hand」列，示例如下：

现有代码问题 #

你当前的SQL使用rows between 1 preceding and 1 preceding，仅能计算前一行的Qty值，无法实现累计求和。

正确实现方式 #

1. 查询累计值

如果仅需要查询出累计结果，使用窗口函数SUM()配合OVER()子句即可：

• 单ItemID全局累计：

SELECT 
    ItemID, 
    Qty,
    SUM(Qty) OVER (ORDER BY ItemID) AS [Stock on hand]
FROM #Stock
ORDER BY ItemID;

• 多ItemID分组累计（每个ItemID单独计算累计）：

SELECT 
    ItemID, 
    Qty,
    SUM(Qty) OVER (PARTITION BY ItemID ORDER BY ItemID) AS [Stock on hand]
FROM #Stock
ORDER BY ItemID;

注意：如果表中有时间字段或其他能确定行顺序的字段，建议将ORDER BY ItemID替换为该字段（如ORDER BY CreateTime），避免因ItemID相同导致的顺序不确定性，确保累计结果准确。

2. 更新表中「Stock on hand」列

如果需要直接更新表中的列，可通过CTE（公共表表达式）结合窗口函数实现：

WITH StockCTE AS (
    SELECT 
        ItemID,
        Qty,
        [Stock on hand],
        SUM(Qty) OVER (PARTITION BY ItemID ORDER BY ItemID) AS NewStockOnHand
    FROM #Stock
)
UPDATE StockCTE
SET [Stock on hand] = NewStockOnHand;

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