如何按组将列中所有NULL值替换为同组非NULL值？

刚好碰到过类似的需求，其实核心就是要获取每组中唯一的非NULL注册号，然后把它填充到同组的NULL位置上。下面分几种常用数据库给你具体的实现方法：

MySQL/MariaDB 实现 #

方法1：窗口函数（MySQL 8.0+ 支持） #

利用窗口函数MAX()在分组内获取非NULL值，再用COALESCE()替换NULL：

SELECT 
  group_id,
  COALESCE(registration_number, MAX(registration_number) OVER (PARTITION BY group_id)) AS registration_number
FROM your_table;

方法2：JOIN 子查询（兼容低版本MySQL） #

先通过分组查询拿到每组的有效注册号，再关联原表替换NULL：

SELECT 
  t.group_id,
  COALESCE(t.registration_number, g.group_reg_num) AS registration_number
FROM your_table t
JOIN (
  SELECT group_id, MAX(registration_number) AS group_reg_num
  FROM your_table
  GROUP BY group_id
) g ON t.group_id = g.group_id;

如果需要直接修改原表数据，可以用UPDATE语句：

UPDATE your_table t
JOIN (
  SELECT group_id, MAX(registration_number) AS group_reg_num
  FROM your_table
  GROUP BY group_id
) g ON t.group_id = g.group_id
SET t.registration_number = g.group_reg_num
WHERE t.registration_number IS NULL;

PostgreSQL 实现 #

PostgreSQL支持多种方式，这里推荐两种简洁的写法：

方法1：MAX窗口函数 #

和MySQL逻辑一致，利用分组内的聚合函数获取有效值：

SELECT 
  group_id,
  COALESCE(registration_number, MAX(registration_number) OVER (PARTITION BY group_id)) AS registration_number
FROM your_table;

方法2：FIRST_VALUE 忽略NULL #

PostgreSQL支持IGNORE NULLS参数，可以直接取分组内第一个非NULL值：

SELECT 
  group_id,
  FIRST_VALUE(registration_number) OVER (PARTITION BY group_id ORDER BY (SELECT NULL) IGNORE NULLS) AS registration_number
FROM your_table;

SQL Server 实现 #

方法1：MAX窗口函数 #

通用且简单的写法，适用于所有版本的SQL Server：

SELECT 
  group_id,
  COALESCE(registration_number, MAX(registration_number) OVER (PARTITION BY group_id)) AS registration_number
FROM your_table;

方法2：FIRST_VALUE 忽略NULL（SQL Server 2022+ 支持） #

如果你的SQL Server版本是2022及以上，可以用更直观的写法：

SELECT 
  group_id,
  FIRST_VALUE(registration_number) OVER (PARTITION BY group_id ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING IGNORE NULLS) AS registration_number
FROM your_table;

原理说明 #

因为你提到每组的注册号要么是一个整数要么全是NULL，所以用MAX()或MIN()聚合函数在分组内获取的结果就是那个唯一的非NULL值；窗口函数可以让我们在原表的每一行都能拿到对应组的有效值，再通过COALESCE()把NULL替换成这个值即可。

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