SQL动态递减计算求助：基于初始值350的累计结果实现

现有表结构 #

需求 #

从初始值350开始，依次减去表中每行的Numbers值，得到每行对应的剩余结果；若计算后剩余结果为0，则终止后续计算。预期结果如下：

原代码问题分析 #

你提供的CTE代码存在以下问题：

1. 查询结果未返回ID和Numbers列，不符合预期的输出结构；
2. 使用关联子查询计算累计和的方式效率较低，数据量大时性能会受影响；
3. 未完整处理“剩余结果为0则终止计算”的逻辑（虽然当前示例数据累计和远小于350，但如果后续行导致剩余值为0，原逻辑无法停止返回后续行）。

原代码：

with cte as (
    select id, (SELECT sum(numbers) from TABLE t2 where t2.id <= t1.id) sumT
    from TABLE T1
  )
select sumT , ( 350 - sumT) from cte where sumT <= 350

修正后的SQL代码 #

使用窗口函数SUM() OVER (ORDER BY id)可以高效计算累计和，同时完整保留所需列，并处理终止逻辑：

WITH running_calc AS (
    SELECT
        id,
        numbers,
        -- 计算当前行的剩余值
        350 - SUM(numbers) OVER (ORDER BY id) AS result,
        -- 计算累计和，用于判断是否终止
        SUM(numbers) OVER (ORDER BY id) AS cumulative_sum
    FROM TABLE
),
-- 处理终止逻辑：只取到剩余值首次<=0的行（含该行），若所有剩余值都>0则返回全部
filtered AS (
    SELECT *,
        -- 标记是否出现剩余值<=0的行
        MAX(CASE WHEN result <= 0 THEN 1 ELSE 0 END) OVER () AS has_zero
    FROM running_calc
)
SELECT id, numbers, result
FROM filtered
WHERE 
    (has_zero = 0) 
    OR (has_zero = 1 AND cumulative_sum <= 350)
ORDER BY id;

结果验证 #

执行上述代码后，会得到与预期完全一致的结果：

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