SQL实现带下限校验的滚动扣减：剩余金额低于Limit时跳过扣减

需求说明 #

需按Sort字段升序逐行判断是否扣减：扣减后剩余金额 >= Limit则执行扣减，否则跳过该行，逐行输出剩余金额RemainingAmount。
普通累加窗口函数无法处理「跳过不符合条件行」的迭代逻辑，需要用**递归CTE（公共表表达式）**实现逐行迭代计算，写法如下：

实现代码 #

WITH RECURSIVE ranked_data AS (
    -- 给所有行按Sort升序编号，确保处理顺序正确
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY Sort) AS rn
    FROM your_table_name -- 替换为实际表名
),
recursive_calc AS (
    -- 锚点：处理Sort最小的第一行
    SELECT 
        rn,
        Sort,
        Amount,
        `Limit`,
        TotalAmount,
        CASE 
            WHEN TotalAmount - Amount >= `Limit` THEN TotalAmount - Amount
            ELSE TotalAmount
        END AS RemainingAmount
    FROM ranked_data
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归：处理后续行，依赖上一行的计算结果
    SELECT 
        curr.rn,
        curr.Sort,
        curr.Amount,
        curr.`Limit`,
        curr.TotalAmount,
        CASE 
            WHEN prev.RemainingAmount - curr.Amount >= curr.`Limit` THEN prev.RemainingAmount - curr.Amount
            ELSE prev.RemainingAmount
        END AS RemainingAmount
    FROM ranked_data curr
    INNER JOIN recursive_calc prev ON curr.rn = prev.rn + 1
)
-- 输出最终结果
SELECT Sort, Amount, `Limit`, TotalAmount, RemainingAmount
FROM recursive_calc
ORDER BY Sort;

逻辑说明 #

1. ranked_data 先给所有行按Sort升序生成连续行号rn，避免Sort字段不连续导致递归逻辑出错。
2. 递归锚点单独处理第一行：用初始TotalAmount判断是否满足扣减条件，得到第一行的剩余金额。
3. 递归部分每次关联上一行的计算结果，用上一行的RemainingAmount判断当前行是否符合扣减要求：符合就扣减金额，不符合就直接继承上一行的剩余金额。
4. 注意Limit是SQL保留字，查询时需要用反引号（MySQL）或双引号（PostgreSQL、Oracle）包裹避免语法报错。

测试结果 #

用示例数据运行代码后输出和预期完全一致：

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