如何消除SQL中CASE语句生成列的额外NULL行并填充有效值

问题背景 #

现有SQL查询返回多行结果，其中各角色列存在大量NULL值，需要将每个项目下的角色有效值填充到所有行，消除NULL。

当前查询语句 #

SELECT * 
FROM 
    (SELECT 
         PRS.[Task_id],
         PE_PRJ.[~Project],
         CASE ROLES.[Task_rolename]
             WHEN N'Primary Doc Controller' THEN USERS.[fullname]
         END AS [Primary Doc Controller],
         CASE ROLES.[Task_rolename]
             WHEN N'Construction Manager' THEN USERS.[fullname]
         END AS [Construction Manager],
         CASE ROLES.[Task_rolename]
             WHEN N'Buyer' THEN USERS.[fullname]
         END AS [Buyer],
         CASE 
             WHEN ROW_NUMBER() OVER (PARTITION BY PE_PRJ.[~Project], ROLES.[Task_rolename] ORDER BY PRS.[createdon]) = COUNT(1) OVER (PARTITION BY PE_PRJ.[~Project], ROLES.[Task_rolename] )
                 THEN 1 
                 ELSE 0 
         END AS [Latest Assignment Record]
     FROM 
         [Task].[Task_projectresources] PRS
     LEFT JOIN
         [Task].[Task_projects] PRJ ON PRS.[Task_project] =  PRJ.[Task_projectsid]
     LEFT JOIN
         [Task].[Task_roles] ROLES ON PRS.[Task_role] =  ROLES.[Task_rolesid]
     LEFT JOIN
         [Task].[Project] PE_PRJ ON FLOOR(PE_PRJ.[~Project]) = FLOOR(PRJ.[Task_project])
     LEFT JOIN
         [Task].[systemuser] USERS ON PRS.[Task_resource] = USERS.[systemuserid]
     WHERE 
         [~Project] = '19691.0019'
    ) LATEST
WHERE 
    LATEST.[Latest Assignment Record] = '1'

当前结果 #

期望结果 #

最优实现方案 #

直接在原查询外层使用窗口聚合函数MAX() OVER (PARTITION BY [~Project])，提取每个项目下各角色的有效值，填充所有行的NULL。

修改后的完整SQL：

SELECT 
    Task_id,
    [~Project],
    MAX([Primary Doc Controller]) OVER (PARTITION BY [~Project]) AS [Primary Doc Controller],
    MAX([Construction Manager]) OVER (PARTITION BY [~Project]) AS [Construction Manager],
    MAX([Buyer]) OVER (PARTITION BY [~Project]) AS [Buyer],
    [Latest Assignment Record]
FROM 
    (SELECT 
         PRS.[Task_id],
         PE_PRJ.[~Project],
         CASE ROLES.[Task_rolename]
             WHEN N'Primary Doc Controller' THEN USERS.[fullname]
         END AS [Primary Doc Controller],
         CASE ROLES.[Task_rolename]
             WHEN N'Construction Manager' THEN USERS.[fullname]
         END AS [Construction Manager],
         CASE ROLES.[Task_rolename]
             WHEN N'Buyer' THEN USERS.[fullname]
         END AS [Buyer],
         CASE 
             WHEN ROW_NUMBER() OVER (PARTITION BY PE_PRJ.[~Project], ROLES.[Task_rolename] ORDER BY PRS.[createdon]) = COUNT(1) OVER (PARTITION BY PE_PRJ.[~Project], ROLES.[Task_rolename] )
                 THEN 1 
                 ELSE 0 
         END AS [Latest Assignment Record]
     FROM 
         [Task].[Task_projectresources] PRS
     LEFT JOIN
         [Task].[Task_projects] PRJ ON PRS.[Task_project] =  PRJ.[Task_projectsid]
     LEFT JOIN
         [Task].[Task_roles] ROLES ON PRS.[Task_role] =  ROLES.[Task_rolesid]
     LEFT JOIN
         [Task].[Project] PE_PRJ ON FLOOR(PE_PRJ.[~Project]) = FLOOR(PRJ.[Task_project])
     LEFT JOIN
         [Task].[systemuser] USERS ON PRS.[Task_resource] = USERS.[systemuserid]
     WHERE 
         [~Project] = '19691.0019'
    ) LATEST
WHERE 
    LATEST.[Latest Assignment Record] = '1'

方案优势 #

1. 性能高效：窗口函数仅需一次数据遍历，比自连接、子查询等多表扫描方式更节省资源，数据量越大优势越明显。
2. 逻辑简洁：利用MAX函数自动忽略NULL的特性，直接提取每个项目下角色的唯一有效值，代码可读性强。
3. 扩展性强：后续新增角色列时，只需复制对应格式的MAX语句即可，无需改动核心逻辑。

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