SQL实现交易表当前行值为null时自动取上一行非空值

方案1：窗口函数实现（推荐，兼容MySQL8+、PostgreSQL、Oracle、SQL Server等主流新版数据库） #

核心逻辑是通过累加标记对连续空值分组，同组共用最近非空值：

SELECT 
    id,
    transaction_date,
    -- 同组取唯一非空值填充
    MAX(balance) OVER (PARTITION BY value_group ORDER BY id) AS filled_balance
FROM (
    SELECT 
        id,
        transaction_date,
        balance,
        -- 遇到非空值累加1，生成分组标记
        SUM(CASE WHEN balance IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY id) AS value_group
    FROM tbl_batch_balances_null
) AS tmp

如果按交易日期排序更符合业务逻辑，可将ORDER BY id替换为ORDER BY transaction_date。该方案性能高，无需硬编码任何行号，完全动态适配全表数据。

方案2：关联子查询实现（兼容不支持窗口函数的旧版数据库，如MySQL5.x） #

SELECT 
    a.id,
    a.transaction_date,
    (
        SELECT balance 
        FROM tbl_batch_balances_null b 
        WHERE b.id <= a.id AND b.balance IS NOT NULL 
        ORDER BY b.id DESC 
        LIMIT 1
    ) AS filled_balance
FROM tbl_batch_balances_null a

该方案兼容性更强，适合小数据量场景使用，大数据量下性能略低于窗口函数方案。

原表更新语句参考 #

如果需要直接将填充后的值更新到原表，可使用以下语句（以MySQL为例）：

UPDATE tbl_batch_balances_null t1
JOIN (
    SELECT 
        id,
        MAX(balance) OVER (PARTITION BY value_group ORDER BY id) AS filled_balance
    FROM (
        SELECT 
            id,
            balance,
            SUM(CASE WHEN balance IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY id) AS value_group
        FROM tbl_batch_balances_null
    ) AS tmp
) t2 ON t1.id = t2.id
SET t1.balance = t2.filled_balance
WHERE t1.balance IS NULL;

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