非唯一ID列下，如何用前一行值填充NAME列的NULL值？

需要处理ID不唯一的表，将NAME列中的NULL值替换为前一行的非NULL值，数据示例如下：

当前数据（AS IS） #

期望结果（TO BE） #

解决方案 #

核心逻辑是按ID分组，给每个非NULL的NAME标记分组，再用分组内的非NULL值填充同组的NULL。注意：必须依赖明确的排序字段（比如自增ID、时间戳）来确定行的顺序，否则"前一行"的定义不成立，下面示例用ROW_NUMBER()生成行号保证顺序，实际使用时可替换为表中真实排序字段。

适用于MySQL 8.0+、PostgreSQL、SQL Server #

方法1：分组标识+FIRST_VALUE

SELECT 
    ID,
    COALESCE(NAME, FIRST_VALUE(NAME) OVER (PARTITION BY ID, grp ORDER BY row_num)) AS NAME
FROM (
    SELECT 
        ID,
        NAME,
        row_num,
        -- 累计计数，遇到非NULL NAME时分组号+1
        SUM(CASE WHEN NAME IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY ID ORDER BY row_num) AS grp
    FROM (
        -- 生成行号，替换为你表中实际排序字段（如create_time）
        SELECT 
            ID,
            NAME,
            ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 1)) AS row_num
        FROM your_table
    ) t1
) t2
ORDER BY row_num;

方法2：LAST_VALUE直接填充

部分数据库支持IGNORE NULLS参数，写法更简洁：

SELECT
    ID,
    LAST_VALUE(NAME IGNORE NULLS) OVER (
        PARTITION BY ID 
        ORDER BY row_num 
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS NAME
FROM (
    SELECT 
        ID,
        NAME,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 1)) AS row_num
    FROM your_table
) t
ORDER BY row_num;

适用于MySQL 5.x（无窗口函数） #

用用户变量实现：

SET @prev_name = NULL;
SET @curr_id = NULL;

SELECT
    ID,
    CASE
        WHEN NAME IS NOT NULL THEN @prev_name := NAME
        WHEN ID = @curr_id THEN @prev_name
        ELSE NULL
    END AS NAME
FROM your_table
ORDER BY row_num; -- 替换为实际排序字段

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