SQL如何实现基于上一行结果的递归公式yₙ₊₁=yₙ+xₙ₊₁计算

该需求完全可以实现，主流SQL数据库支持两种常见实现方式：

方案1：窗口累加和实现（最简洁，兼容MySQL 8+、PostgreSQL、SQL Server等绝大多数支持窗口函数的数据库） #

直接利用窗口函数的逐行累加特性，代码如下：

with
    init as (
        select 0.1 as y0
    ),
    cte as (
        select 1 as i, 1 as x   -- x_1
        union all
        select 2 as i, 10 as x  -- x_2
        union all 
        select 3 as i, 100 as x -- x_3
    )
select 
    cte.x, 
    init.y0 + sum(cte.x) over (order by cte.i asc) as y
from cte
join init on true
order by cte.i asc

运行后返回的y值就是你需要的1.1、11.1、111.1。

方案2：递归CTE实现（贴合递归公式逻辑，适合更复杂的迭代计算场景） #

如果你需要严格按照递归公式的逻辑实现，可以用递归CTE写法：

with recursive
    init as (
        select 0.1 as y0
    ),
    cte as (
        select 1 as i, 1 as x   -- x_1
        union all
        select 2 as i, 10 as x  -- x_2
        union all 
        select 3 as i, 100 as x -- x_3
    ),
    recursive_calc as (
        -- 锚点：计算y1
        select cte.i, cte.x, init.y0 + cte.x as y
        from cte
        join init on true
        where cte.i = 1
        union all
        -- 递归部分：计算y_{n+1} = y_n + x_{n+1}
        select cte.i, cte.x, recursive_calc.y + cte.x as y
        from recursive_calc
        join cte on cte.i = recursive_calc.i + 1
    )
select x, y from recursive_calc order by i asc

两种方案均可得到你需要的结果，其中窗口函数方案性能更优，适合数据量较大的场景；递归CTE方案灵活性更高，可适配规则更复杂的迭代计算需求。

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