请求编写SQL脚本填充SQL Server 2019中Audit表的NULL值

我有一个Microsoft SQL Server 2019数据库，包含两张核心表：

• Employee表：字段为EmployeeId（自增主键）、Salary、Title
• Audit表：字段为AuditId（自增主键）、EmployeeId（关联Employee表）、Salary、Title、Timestamp

Audit表用于记录Employee表的变更历史：仅当Employee的Salary或Title发生修改时，Audit中对应字段才会赋值，未变更的字段则为NULL。现在需要编写T-SQL脚本完成两步填充逻辑：

1. 优先从Audit表的历史行中填充NULL值（取该员工之前最近一次非NULL的对应字段值）
2. 若第一步后仍有NULL值，说明该字段从未被修改过，从Employee表取当前值补充

现有方案分析 #

你尝试用窗口函数MAX()来填充历史非NULL值，思路方向正确，但存在两个关键问题：

1. MAX()仅能保留分区内的最大值，无法准确跟踪最新的变更（比如Salary被调低时，MAX()会保留旧的高值，而非最新修改值）
2. 未处理第一步后仍为NULL的场景，无法从Employee表获取从未修改过字段的初始值

完整解决方案脚本 #

我们可以利用SQL Server 2019支持的LAST_VALUE()+IGNORE NULLS语法，更精准地获取最近的非NULL历史值，再结合Employee表完成最终填充：

-- 第一步：从Audit历史数据中填充NULL（取最近一次非NULL的字段值）
WITH AuditFilledFromHistory AS (
    SELECT 
        AuditId,
        EmployeeId,
        LAST_VALUE(Salary) OVER (
            PARTITION BY EmployeeId 
            ORDER BY Timestamp 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            IGNORE NULLS
        ) AS FilledSalary,
        LAST_VALUE(Title) OVER (
            PARTITION BY EmployeeId 
            ORDER BY Timestamp 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            IGNORE NULLS
        ) AS FilledTitle
    FROM Audit
),
-- 第二步：若历史填充后仍为NULL，从Employee表取当前值补充
AuditFinalFilled AS (
    SELECT 
        afh.AuditId,
        ISNULL(afh.FilledSalary, e.Salary) AS FinalSalary,
        ISNULL(afh.FilledTitle, e.Title) AS FinalTitle
    FROM AuditFilledFromHistory afh
    JOIN Employee e ON afh.EmployeeId = e.EmployeeId
)
-- 更新Audit表的最终填充值
UPDATE a
SET 
    Salary = aff.FinalSalary,
    Title = aff.FinalTitle
FROM Audit a
JOIN AuditFinalFilled aff ON a.AuditId = aff.AuditId;

关键细节说明 #

• LAST_VALUE(...) IGNORE NULLS：会自动跳过NULL值，直接取当前行之前最近的非NULL字段值，完全贴合"跟踪最新变更"的业务需求
• ISNULL()判断：如果历史填充后字段仍为NULL，说明该字段从未被修改，直接关联Employee表取当前值补充
• 分CTE拆分逻辑：将两步填充逻辑分离，代码更清晰易维护，也避免一次性更新带来的逻辑冲突

兼容低版本方案（若不支持IGNORE NULLS） #

如果你的SQL Server版本不支持IGNORE NULLS，可以用OUTER APPLY替代获取最近的非NULL历史值：

WITH AuditFilledFromHistory AS (
    SELECT 
        a.AuditId,
        a.EmployeeId,
        COALESCE(a.Salary, prevSal.Salary) AS FilledSalary,
        COALESCE(a.Title, prevTit.Title) AS FilledTitle
    FROM Audit a
    -- 获取当前行之前最近的非NULL Salary
    OUTER APPLY (
        SELECT TOP 1 Salary 
        FROM Audit 
        WHERE EmployeeId = a.EmployeeId AND Timestamp < a.Timestamp AND Salary IS NOT NULL
        ORDER BY Timestamp DESC
    ) prevSal
    -- 获取当前行之前最近的非NULL Title
    OUTER APPLY (
        SELECT TOP 1 Title 
        FROM Audit 
        WHERE EmployeeId = a.EmployeeId AND Timestamp < a.Timestamp AND Title IS NOT NULL
        ORDER BY Timestamp DESC
    ) prevTit
),
AuditFinalFilled AS (
    SELECT 
        afh.AuditId,
        ISNULL(afh.FilledSalary, e.Salary) AS FinalSalary,
        ISNULL(afh.FilledTitle, e.Title) AS FinalTitle
    FROM AuditFilledFromHistory afh
    JOIN Employee e ON afh.EmployeeId = e.EmployeeId
)
UPDATE a
SET 
    Salary = aff.FinalSalary,
    Title = aff.FinalTitle
FROM Audit a
JOIN AuditFinalFilled aff ON a.AuditId = aff.AuditId;

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