如何在SQL中基于上一行计算自定义列f的值？

安全阿岳

2026-8-20

问题说明

我有一张名为van的表，表内包含列a（以及用于确定行顺序的字段，比如自增ID）。需要通过SELECT语句新增一列f，该列按以下规则计算：

初始值：f[0] = 0
递推公式：f[i] = (f[i-1] * 13 + a[i]) / 14

实现方案

根据不同数据库类型，提供以下几种可行的SQL写法：

1. MySQL/MariaDB 写法

利用用户变量存储上一行的f值，注意必须指定排序字段保证计算顺序：

SELECT 
    id,
    a,
    @f := (@f * 13 + a) / 14 AS f
FROM 
    van,
    (SELECT @f := 0) AS init
ORDER BY 
    id; -- 替换为你表中实际的排序字段，比如时间戳、自增ID

2. PostgreSQL 写法

使用递归CTE实现递推计算：

WITH RECURSIVE van_ordered AS (
    SELECT 
        a,
        id,
        ROW_NUMBER() OVER (ORDER BY id) AS rn -- 生成行号确定计算顺序
    FROM van
),
recursive_f AS (
    SELECT 
        id,
        a,
        0::numeric AS f, -- 初始f值
        rn
    FROM van_ordered
    WHERE rn = 1
    UNION ALL
    SELECT 
        vo.id,
        vo.a,
        (rf.f * 13 + vo.a) / 14 AS f,
        vo.rn
    FROM van_ordered vo
    JOIN recursive_f rf ON vo.rn = rf.rn + 1
)
SELECT id, a, f FROM recursive_f ORDER BY id;

3. SQL Server 写法

同样通过递归CTE实现：

WITH van_ordered AS (
    SELECT 
        a,
        id,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM van
),
recursive_f AS (
    SELECT 
        id,
        a,
        CAST(0 AS DECIMAL(18,6)) AS f,
        rn
    FROM van_ordered
    WHERE rn = 1
    UNION ALL
    SELECT 
        vo.id,
        vo.a,
        CAST((rf.f * 13 + vo.a) / 14 AS DECIMAL(18,6)) AS f,
        vo.rn
    FROM van_ordered vo
    INNER JOIN recursive_f rf ON vo.rn = rf.rn + 1
)
SELECT id, a, f FROM recursive_f ORDER BY id;