SQL Server中按指定规则向前填充行值的SQL代码需求

针对你的需求，我们可以通过窗口函数结合分组标记的方式实现指定区间非空值的向前填充，以下是SQL代码：

WITH numbered_rows AS (
    -- 按MonthEndDate排序生成行号，明确行的顺序
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY MonthEndDate) AS rn
    FROM Table1
),
-- 处理employeeId列：仅用第4-16行的非空值向前填充
employee_fill AS (
    SELECT *,
           -- 标记第4-16行中的非空employeeId为有效填充源
           CASE WHEN rn BETWEEN 4 AND 16 AND employeeId IS NOT NULL THEN employeeId END AS valid_employeeId,
           -- 生成分组：每遇到一个有效填充源，分组编号递增
           COUNT(CASE WHEN rn BETWEEN 4 AND 16 AND employeeId IS NOT NULL THEN 1 END) OVER (ORDER BY rn) AS employee_group
    FROM numbered_rows
),
employee_filled AS (
    SELECT *,
           -- 每个分组内取有效填充值，覆盖该分组内的空值
           MAX(valid_employeeId) OVER (PARTITION BY employee_group) AS filled_employeeId
    FROM employee_fill
),
-- 处理jobDescription列：仅用第4-16行的非空值向前填充
jobdesc_fill AS (
    SELECT *,
           CASE WHEN rn BETWEEN 4 AND 16 AND jobDescription IS NOT NULL THEN jobDescription END AS valid_jobDescription,
           COUNT(CASE WHEN rn BETWEEN 4 AND 16 AND jobDescription IS NOT NULL THEN 1 END) OVER (ORDER BY rn) AS jobdesc_group
    FROM employee_filled
),
jobdesc_filled AS (
    SELECT *,
           MAX(valid_jobDescription) OVER (PARTITION BY jobdesc_group) AS filled_jobDescription
    FROM jobdesc_fill
),
-- 处理StaffTypeID列：仅用第1-12行的非空值向前填充
stafftype_fill AS (
    SELECT *,
           CASE WHEN rn BETWEEN 1 AND 12 AND StaffTypeID IS NOT NULL THEN StaffTypeID END AS valid_StaffTypeID,
           COUNT(CASE WHEN rn BETWEEN 1 AND 12 AND StaffTypeID IS NOT NULL THEN 1 END) OVER (ORDER BY rn) AS stafftype_group
    FROM jobdesc_filled
),
stafftype_filled AS (
    SELECT *,
           MAX(valid_StaffTypeID) OVER (PARTITION BY stafftype_group) AS filled_StaffTypeID
    FROM stafftype_fill
),
-- 处理Description列：仅用第1-12行的非空值向前填充
desc_fill AS (
    SELECT *,
           CASE WHEN rn BETWEEN 1 AND 12 AND Description IS NOT NULL THEN Description END AS valid_Description,
           COUNT(CASE WHEN rn BETWEEN 1 AND 12 AND Description IS NOT NULL THEN 1 END) OVER (ORDER BY rn) AS desc_group
    FROM stafftype_filled
),
desc_filled AS (
    SELECT *,
           MAX(valid_Description) OVER (PARTITION BY desc_group) AS filled_Description
    FROM desc_fill
)
-- 生成最终结果表Table2，保留原非空值，替换空值为填充后的值
SELECT
    MonthEndDate,
    COALESCE(employeeId, filled_employeeId) AS employeeId,
    COALESCE(jobDescription, filled_jobDescription) AS jobDescription,
    COALESCE(StaffTypeID, filled_StaffTypeID) AS StaffTypeID,
    COALESCE(Description, filled_Description) AS Description
INTO Table2
FROM desc_filled;

代码逻辑说明 #

1. 行号标记：通过ROW_NUMBER()按MonthEndDate生成行号，确保行的顺序符合需求。
2. 有效填充源标记：针对每个列，仅将指定行区间内的非空值标记为有效填充源。
3. 分组生成：用COUNT()窗口函数创建分组，每次遇到有效填充源时分组编号递增，确保填充值仅在对应分组内传播。
4. 值填充：用MAX()窗口函数在每个分组内提取有效填充值，覆盖分组内的空值。
5. 结果生成：通过COALESCE()保留原列的非空值，将空值替换为填充后的值，最终插入到Table2中。

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