如何用T-SQL实现基于上一行NetWeight更新当前行NetWeight？

你之前用CTE+LAG函数失败的原因是：LAG取的是表中原始的上一行NetWeight值，而更新操作是批量执行的，所有行的更新逻辑都基于更新前的数据，导致只有第一行能正确更新，后续行没法用到前一行更新后的结果。

下面给两种靠谱的解决方案：

方案1：用窗口函数计算累计和（推荐，效率更高） #

其实你要的NetWeight本质就是TransactionWeight的累计求和值，直接用窗口函数SUM() OVER()一次性算出所有行的正确结果，再批量更新即可：

WITH CTE AS (
    SELECT 
        Id,
        NetWeight,
        -- 按Id排序，计算从第一行到当前行的TransactionWeight累计和
        SUM(TransactionWeight) OVER (ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS NewNetWeight
    FROM Table1
)
UPDATE CTE
SET NetWeight = NewNetWeight;

这个方法不需要逐行依赖，所有行的正确值一次性计算完成，更新效率远高于游标。

方案2：用游标逐行更新（适合复杂自定义逻辑） #

如果你的业务逻辑比单纯累计更复杂，必须逐行使用前一行更新后的结果，可以用游标实现：

DECLARE @PrevNetWeight DECIMAL(18,2)
DECLARE @CurrentId INT
DECLARE @CurrentTransWeight DECIMAL(18,2)

-- 按Id顺序获取所有行数据
DECLARE UpdateCursor CURSOR FOR
SELECT Id, TransactionWeight
FROM Table1
ORDER BY Id

OPEN UpdateCursor
-- 取第一行初始化
FETCH NEXT FROM UpdateCursor INTO @CurrentId, @CurrentTransWeight
SET @PrevNetWeight = @CurrentTransWeight
UPDATE Table1 SET NetWeight = @PrevNetWeight WHERE Id = @CurrentId

-- 循环处理剩余行
FETCH NEXT FROM UpdateCursor INTO @CurrentId, @CurrentTransWeight
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @PrevNetWeight = @PrevNetWeight + @CurrentTransWeight
    UPDATE Table1 SET NetWeight = @PrevNetWeight WHERE Id = @CurrentId
    FETCH NEXT FROM UpdateCursor INTO @CurrentId, @CurrentTransWeight
END

-- 清理游标
CLOSE UpdateCursor
DEALLOCATE UpdateCursor

游标是逐行处理的，每一行更新都会基于前一行更新后的NetWeight值，完全符合你的需求，但注意游标在数据量大时效率较低，优先用方案1。

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