基于同表非空值更新数据仓库历史表中遗漏的YEAR_OF_BIRTH列

这是个很常见的批量更新场景——要把历史表中员工的缺失出生日期，用该员工已有的非空值补全。下面给你两种实用的SQL方案，适用于大多数主流数据库（MySQL、PostgreSQL、SQL Server等）：

方案1：自连接分组更新（兼容性最强） #

这种方法通过子查询先提取每个员工的有效出生日期，再关联原表更新NULL值，几乎所有数据库都支持：

-- 替换your_table为你的实际表名
UPDATE your_table t1
JOIN (
    -- 按员工分组，取出每个员工的非空出生日期（MAX/MIN都可，因为每个员工只有一个有效生日）
    SELECT employee_id, MAX(year_of_birth) AS valid_birth_year
    FROM your_table
    WHERE year_of_birth IS NOT NULL
    GROUP BY employee_id
) t2 ON t1.employee_id = t2.employee_id
-- 只更新YEAR_OF_BIRTH为NULL的行
SET t1.year_of_birth = t2.valid_birth_year
WHERE t1.year_of_birth IS NULL;

方案2：窗口函数更新（适合支持窗口函数的数据库） #

如果你的数据库支持窗口函数（比如MySQL 8.0+、PostgreSQL 9.4+、SQL Server 2012+），可以用更简洁的方式：

-- MySQL 8.0+ 版本写法
UPDATE your_table
SET year_of_birth = (
    SELECT FIRST_VALUE(year_of_birth) OVER (
        PARTITION BY employee_id 
        ORDER BY year_of_birth IS NULL -- 让非空值排在最前面
    )
    FROM your_table t2
    WHERE t2.employee_id = your_table.employee_id
    LIMIT 1
)
WHERE year_of_birth IS NULL;

验证逻辑（更新前必做！） #

执行更新前，建议先运行SELECT语句验证结果是否符合预期：

SELECT 
    id,
    employee_id,
    COALESCE(year_of_birth, (
        SELECT MAX(year_of_birth) 
        FROM your_table t2 
        WHERE t2.employee_id = your_table.employee_id
    )) AS year_of_birth,
    project_id
FROM your_table;

把你的测试数据代入的话，这个查询会直接返回你期望的结果，确认没问题后再执行UPDATE更稳妥。

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