基于上一行计算更新SQL Server表中各user_id对应数据

这是一个典型的递归行依赖计算场景——因为每行的opening_balance完全依赖上一行的closing_balance，没法用普通聚合或窗口函数一步到位，必须用迭代式的方法逐行计算。下面针对主流数据库给出具体实现方案：

1. PostgreSQL / MySQL 8.0+ / SQL Server（递归CTE方案） #

假设你的表名为user_transactions，包含字段：user_id（用户ID）、txn_id（用于确定同一用户下的行顺序，比如自增交易ID或交易时间，必须有序）、opening_balance（仅第一行有初始值）、Debit（借方金额）、Credit（贷方金额）、interest_rate（利率），需要计算interim_balance（临时余额）、calculated_interest（计算利息）、closing_balance（期末余额）。

WITH RECURSIVE user_calculations AS (
    -- 锚点成员：获取每个用户的第一行数据，完成初始计算
    SELECT
        user_id,
        txn_id,
        opening_balance,
        Debit,
        Credit,
        interest_rate,
        opening_balance + Debit + Credit AS interim_balance,
        (opening_balance + Debit + Credit) * interest_rate AS calculated_interest,
        (opening_balance + Debit + Credit) + ((opening_balance + Debit + Credit) * interest_rate) AS closing_balance
    FROM user_transactions
    WHERE txn_id = (SELECT MIN(txn_id) FROM user_transactions ut WHERE ut.user_id = user_transactions.user_id)

    UNION ALL

    -- 递归成员：逐行计算后续数据，用上一行的closing_balance作为当前行的opening_balance
    SELECT
        ut.user_id,
        ut.txn_id,
        uc.closing_balance AS opening_balance,
        ut.Debit,
        ut.Credit,
        ut.interest_rate,
        uc.closing_balance + ut.Debit + ut.Credit AS interim_balance,
        (uc.closing_balance + ut.Debit + ut.Credit) * ut.interest_rate AS calculated_interest,
        (uc.closing_balance + ut.Debit + ut.Credit) + ((uc.closing_balance + ut.Debit + ut.Credit) * ut.interest_rate) AS closing_balance
    FROM user_transactions ut
    JOIN user_calculations uc 
        ON ut.user_id = uc.user_id 
        AND ut.txn_id = (SELECT MIN(txn_id) FROM user_transactions ut2 WHERE ut2.user_id = ut.user_id AND ut2.txn_id > uc.txn_id)
)
-- 将计算结果更新回原表
UPDATE user_transactions ut
SET
    opening_balance = uc.opening_balance,
    interim_balance = uc.interim_balance,
    calculated_interest = uc.calculated_interest,
    closing_balance = uc.closing_balance
FROM user_calculations uc
WHERE ut.user_id = uc.user_id AND ut.txn_id = uc.txn_id;

关键说明： #

• 必须有唯一且有序的字段（比如txn_id）来确定同一用户下的行顺序，否则递归计算会乱序出错。
• 锚点成员负责初始化每个用户的第一行计算，递归成员则通过关联上一行结果，完成后续行的迭代计算。
• 建议先单独运行SELECT * FROM user_calculations ORDER BY user_id, txn_id;验证计算结果，确认无误后再执行UPDATE操作。

2. MySQL 5.x（无递归CTE支持，用变量模拟） #

如果你的MySQL版本低于8.0，无法使用递归CTE，可以用用户变量模拟迭代逻辑：

-- 按user_id和txn_id排序，确保计算顺序正确
UPDATE user_transactions ut
JOIN (
    SELECT
        user_id,
        txn_id,
        @prev_closing := IF(@current_user = user_id, @prev_closing, opening_balance) AS opening_balance,
        @prev_closing + Debit + Credit AS interim_balance,
        (@prev_closing + Debit + Credit) * interest_rate AS calculated_interest,
        @prev_closing := (@prev_closing + Debit + Credit) + ((@prev_closing + Debit + Credit) * interest_rate) AS closing_balance,
        @current_user := user_id
    FROM user_transactions,
         (SELECT @current_user := NULL, @prev_closing := 0) vars
    ORDER BY user_id, txn_id
) calc ON ut.user_id = calc.user_id AND ut.txn_id = calc.txn_id
SET
    ut.opening_balance = calc.opening_balance,
    ut.interim_balance = calc.interim_balance,
    ut.calculated_interest = calc.calculated_interest,
    ut.closing_balance = calc.closing_balance;

注意事项： #

• 这种方法依赖MySQL变量的赋值顺序，必须严格按ORDER BY user_id, txn_id保证计算顺序。
• 大表场景下变量方法性能和可读性都不如递归CTE，建议优先升级到MySQL 8.0+使用递归方案。

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