如何编写SQL及动态SQL将表中所有列空值替换为上一条非空记录

固定列场景（已知需补全的列名） #

如果已经明确要补全的列（如示例中的June、July、August），有两种实现方式：

窗口函数高效写法（支持IGNORE NULLS的数据库适用，如SQL Server 2022+、PostgreSQL 11+、MySQL 8.0+等） #

这种写法性能远高于嵌套子查询，直接调用内置函数实现取最近非空值的逻辑：

SELECT 
    Date,
    LAST_VALUE(June) IGNORE NULLS OVER (ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS June,
    LAST_VALUE(July) IGNORE NULLS OVER (ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS July,
    LAST_VALUE(August) IGNORE NULLS OVER (ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS August
FROM Temp
ORDER BY Date

兼容旧版本的扩展写法（适配你原有的OUTER APPLY逻辑） #

如果数据库不支持IGNORE NULLS，直接在原有单字段逻辑上，为每个需补全的列新增对应的OUTER APPLY逻辑即可：

SELECT  
    a.Date,
    ISNULL(a.June, j.June) AS June,
    ISNULL(a.July, jy.July) AS July,
    ISNULL(a.August, ag.August) AS August
FROM Temp a
-- 补全June列
OUTER APPLY (
    SELECT TOP 1 June
    FROM Temp b 
    WHERE b.DATE < a.DATE AND b.June IS NOT NULL
    ORDER BY DATE DESC
) j
-- 补全July列
OUTER APPLY (
    SELECT TOP 1 July
    FROM Temp b 
    WHERE b.DATE < a.DATE AND b.July IS NOT NULL
    ORDER BY DATE DESC
) jy
-- 补全August列
OUTER APPLY (
    SELECT TOP 1 August
    FROM Temp b 
    WHERE b.DATE < a.DATE AND b.August IS NOT NULL
    ORDER BY DATE DESC
) ag
ORDER BY a.date

动态SQL多列批量适配场景（列不固定/列数较多） #

如果需要自动适配表中所有需补全的列，不用手动逐个写列逻辑，可以用动态SQL批量生成查询语句。以下是SQL Server下基于窗口函数的动态实现示例：

DECLARE @sql NVARCHAR(MAX)
-- 遍历所有需补全的列，排除不需要补全的日期列Date
SELECT @sql = COALESCE(@sql + ',','') + 'LAST_VALUE(' + QUOTENAME(name) + ') IGNORE NULLS OVER (ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ' + QUOTENAME(name)
FROM sys.columns 
WHERE object_id = OBJECT_ID('Temp') AND name <> 'Date'

-- 拼接完整查询语句
SET @sql = 'SELECT Date, ' + @sql + ' FROM Temp ORDER BY Date'

-- 执行生成的SQL
EXEC sp_executesql @sql

如果需要适配你原有的OUTER APPLY逻辑，只要修改动态拼接规则，为每个列生成对应的OUTER APPLY和SELECT字段逻辑即可。

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