SQL Server中如何向下填充Unit No、租户名及保证金字段

输入数据 #

输出结果 #

需求说明 #

在SQL Server中，需实现Unit No、Tenant Name（租户名称）及Security Deposit（保证金）字段的向下填充操作，仅依赖Unit No作为唯一标识列。场景允许同一租户名称对应不同单元号，且Id为9和10的记录需合并为同一租户名称。

解决方案SQL代码 #

WITH CTE_FillGroups AS (
    -- 以非空Unit No为分界，划分填充分组
    SELECT 
        Id,
        [Unit No] AS UnitNo,
        [Tenant Name] AS TenantName,
        [Net Rent] AS NetRent,
        [Security Deposit] AS SecurityDeposit,
        COUNT(CASE WHEN [Unit No] IS NOT NULL AND [Unit No] <> '' THEN 1 END) OVER (ORDER BY Id) AS GroupId
    FROM YourTableName
),
CTE_GroupBaseValues AS (
    -- 获取每个分组的基准填充值
    SELECT 
        GroupId,
        MAX([Unit No]) AS FilledUnitNo,
        MAX([Tenant Name]) AS FilledTenantName,
        MAX([Security Deposit]) AS FilledSecurityDeposit
    FROM CTE_FillGroups
    GROUP BY GroupId
)
SELECT 
    f.Id,
    -- 填充空的Unit No
    CASE WHEN f.UnitNo IS NULL OR f.UnitNo = '' THEN g.FilledUnitNo ELSE f.UnitNo END AS [Unit No],
    -- 填充空的Tenant Name，处理Id=10的特殊场景
    CASE 
        WHEN f.TenantName IS NULL OR f.TenantName = '' THEN 
            COALESCE(g.FilledTenantName, 
                     LAST_VALUE(f.TenantName) OVER (ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
        ELSE f.TenantName 
    END AS [Tenant Name],
    f.NetRent AS [Net Rent],
    -- 填充空的Security Deposit
    CASE WHEN f.SecurityDeposit IS NULL THEN g.FilledSecurityDeposit ELSE f.SecurityDeposit END AS [Security Deposit]
FROM CTE_FillGroups f
JOIN CTE_GroupBaseValues g ON f.GroupId = g.GroupId
ORDER BY f.Id;

代码说明 #

1. CTE_FillGroups：利用COUNT() OVER窗口函数，为连续的需填充记录分配分组ID，非空的Unit No作为分组起始标记。
2. CTE_GroupBaseValues：提取每个分组内的非空基准值，作为填充的来源。
3. 主查询：关联分组基准值完成常规填充；针对Id=10的情况，通过COALESCE结合LAST_VALUE确保空租户名称能取到上一个有效值。

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