如何在SQL中实现带利率更新的递进累计求和？

需求说明 #

需计算SumC列，规则如下：

• 第1天SumC等于当日VALUE_A（对应表中amount字段）
• 后续每日：
  • 若RATE_A（对应表中rate字段）不为0，SumC = 前一日SumC * (1 + RATE_A/100)
  • 若RATE_A为0，SumC = 前一日SumC + 当日VALUE_A

测试表结构与数据 #

CREATE TABLE `New_Temp`.`Temp` (
  `day_no` INT NULL,
  `rate` INT NULL,
  `amount` INT NULL);

INSERT INTO `New_Temp`.`Temp` (`day_no`,`rate`,`amount`)
VALUES  (1,0,50)
        ,(2,0,40)
        ,(3,6,0)
        ,(4,0,20)
        ,(5,0,10)
        ,(6,8,0)
        ,(7,0,5);

期望结果 #

注：第6天SumC实际计算值为125.4 * 1.08 = 135.432，原期望结果的135.4为近似值，可通过调整小数精度匹配需求。

解决方案1：递归CTE（适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

递归CTE是处理这类行依赖递推计算的最优方案，能逐行传递前一日结果完成计算：

WITH RECURSIVE daily_calc AS (
    -- 初始化：取第一天数据，直接用amount作为初始SumC
    SELECT 
        day_no,
        rate AS RATE_A,
        amount AS VALUE_A,
        CAST(amount AS DECIMAL(10,2)) AS SumC
    FROM Temp
    WHERE day_no = 1
    
    UNION ALL
    
    -- 递归计算后续日期
    SELECT 
        t.day_no,
        t.rate AS RATE_A,
        t.amount AS VALUE_A,
        CASE
            WHEN t.rate != 0 THEN CAST(dc.SumC * (1 + t.rate/100.0) AS DECIMAL(10,2))
            ELSE CAST(dc.SumC + t.amount AS DECIMAL(10,2))
        END AS SumC
    FROM Temp t
    JOIN daily_calc dc ON t.day_no = dc.day_no + 1
)
SELECT 
    day_no,
    CONCAT(IF(RATE_A != 0, CONCAT(RATE_A, '%'), '0')) AS RATE_A,
    VALUE_A,
    SumC
FROM daily_calc
ORDER BY day_no;

解决方案2：用户变量递推（适用于MySQL 5.x等不支持递归的版本） #

通过用户变量传递前一日计算结果，依赖排序确保计算顺序：

SELECT 
    day_no,
    CONCAT(IF(rate != 0, CONCAT(rate, '%'), '0')) AS RATE_A,
    amount AS VALUE_A,
    @sumc := CASE
        WHEN rate != 0 THEN @sumc * (1 + rate/100.0)
        ELSE @sumc + amount
    END AS SumC
FROM Temp, (SELECT @sumc := 0) AS init
ORDER BY day_no;

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