SQL中如何将同分组内NULL值替换为同组首行非NULL值

已知条件 #

表结构包含column1、column2两列：

• column2为组内序号，每个分组从1递增到5循环
• 每个分组仅column2=1的行column1有有效值，其余行column1为NULL
  原始数据样例：

| column1           | column2 |
|-------------------|---------|
| 1st,hanoi,vietnam | 1       |
| null              | 2       |
| null              | 3       |
| null              | 4       |
| null              | 5       |
| 2st,hanoi,vietnam | 1       |
| null              | 2       |
| null              | 3       |
| null              | 4       |
| null              | 5       |

目标输出为同组所有行的column1填充为该组column2=1对应的有效值，样例如下：

| column1           | column2 |
|-------------------|---------|
| 1st,hanoi,vietnam | 1       |
| 1st,hanoi,vietnam | 2       |
| 1st,hanoi,vietnam | 3       |
| 1st,hanoi,vietnam | 4       |
| 1st,hanoi,vietnam | 5       |
| 2st,hanoi,vietnam | 1       |
| 2st,hanoi,vietnam | 2       |
| 2st,hanoi,vietnam | 3       |
| 2st,hanoi,vietnam | 4       |
| 2st,hanoi,vietnam | 5       |

原有写法问题说明 #

• STRING_AGG是多行聚合函数，会将指定范围内的字符串合并为单个值，没有逐行匹配填充的逻辑，加上UNION操作会对结果集去重合并，完全不符合逐行填充的需求。
• ISNULL(column1, (SELECT TOP 1 [column1]))的子查询没有限定分组范围和排序规则，只会返回全表第一行的column1值，无法按组匹配对应的填充值。

正确实现方案 #

核心逻辑：先通过窗口函数给每一行打上所属分组的唯一标识，再取同组内的非空column1值填充即可，不需要硬编码5行一组的规则，后续分组行数调整也可以正常运行。

通用写法（支持MySQL 8.0+、PostgreSQL、SQL Server 2012+、Hive、Spark SQL等所有支持标准窗口函数的数据库） #

WITH tmp AS (
    SELECT
        column1,
        column2,
        -- 每遇到一次column2=1，分组ID+1，标记当前行所属分组
        SUM(CASE WHEN column2 = 1 THEN 1 ELSE 0 END) OVER (
            ORDER BY 表的顺序字段 -- 替换为实际确定行顺序的字段，如自增ID、创建时间
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id
    FROM 你的表名
)
SELECT
    -- 取同组内的非空column1作为填充值
    MAX(column1) OVER (PARTITION BY group_id) AS column1,
    column2
FROM tmp;

注意：必须指定实际的排序字段（如自增主键ID、数据入库时间），否则数据库返回行顺序不确定，会导致分组标记错误。

固定5行分组简化写法 #

如果确定分组规则永远是5行一组，可以直接通过序号计算分组ID，写法更简洁：

SELECT
    MAX(column1) OVER (PARTITION BY CEIL(顺序字段/5)) AS column1,
    column2
FROM 你的表名;

SQL Server低版本兼容写法（不支持窗口函数累计计算场景） #

SELECT
    t1.column2,
    t2.column1
FROM 你的表名 t1
CROSS APPLY (
    SELECT TOP 1 column1
    FROM 你的表名 t2
    WHERE t2.顺序字段 <= t1.顺序字段 AND t2.column1 IS NOT NULL
    ORDER BY t2.顺序字段 DESC
) t2;

内容的提问来源于stack exchange，提问作者Phạm Nam Khánh