如何在SQL中逐行递减金额并更新result列？

嘿，我来帮你搞定这个逐行递减金额更新result列的需求！先理清楚我们要实现的逻辑：第一行的result固定为0，从第二行开始，每一行的result等于上一行的result减去当前行的amount（本质上就是用第一行的amount依次减去后续每一行的amount，得到累计剩余值）。

先确认下初始数据和期望结果：

初始数据 #

id  amount  result
1   3000    
2   1000    
3   500     
4   1000    

期望结果 #

id  amount  result
1   3000    0
2   1000    2000
3   500     1500
4   1000    500

下面分不同数据库版本给你提供解决方案：

方案1：支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server等） #

用窗口函数直接计算累计值，效率很高，适合大数据量场景：

WITH calculated_result AS (
    SELECT 
        id,
        amount,
        CASE 
            WHEN id = 1 THEN 0
            -- 用第一行的amount减去从第二行到当前行的amount总和
            ELSE (SELECT amount FROM your_table WHERE id = 1) - SUM(amount) OVER (ORDER BY id ROWS BETWEEN 2 AND CURRENT ROW)
        END AS new_result
    FROM your_table
)
UPDATE your_table t
SET result = cr.new_result
FROM calculated_result cr
WHERE t.id = cr.id;

方案2：递归CTE实现（支持递归的数据库） #

如果更倾向于直观的逐行递推逻辑，可以用递归CTE，尤其适合id不连续的情况：

WITH numbered_rows AS (
    -- 先给每行按id排序分配行号，避免id不连续的问题
    SELECT 
        id,
        amount,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM your_table
),
RECURSIVE recursive_result AS (
    -- 基础情况：第一行的result设为0
    SELECT 
        id,
        amount,
        rn,
        0 AS result
    FROM numbered_rows
    WHERE rn = 1
    UNION ALL
    -- 递归计算：后续行的result = 上一行的result - 当前行的amount
    SELECT 
        nr.id,
        nr.amount,
        nr.rn,
        rr.result - nr.amount AS result
    FROM numbered_rows nr
    JOIN recursive_result rr ON nr.rn = rr.rn + 1
)
UPDATE your_table t
SET result = rr.result
FROM recursive_result rr
WHERE t.id = rr.id;

方案3：MySQL 5.x版本（不支持窗口函数和递归） #

用用户变量来实现逐行计算，注意必须加上ORDER BY id保证计算顺序：

-- 先获取第一行的amount作为初始值
SET @prev_result = (SELECT amount FROM your_table WHERE id = 1);

-- 按id顺序更新result列
UPDATE your_table 
SET result = CASE 
    WHEN id = 1 THEN 0
    ELSE @prev_result := @prev_result - amount
END
ORDER BY id;

测试一下这些语句，都能得到你期望的结果哦！

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