使用ROW_NUMBER()分组排序后，如何返回各列非空有效值？

核心思路是先聚合分组内各列的非空值，再和排名第一的行关联，用聚合值替换原行的NULL。以下是具体实现步骤：

1. 示例表结构参考 #

假设你的临时表结构如下（可根据实际列调整）：

CREATE TABLE #FakeTable (
    PersonID INT,
    KeyFieldCNT INT,
    Col1 VARCHAR(50),
    Col2 INT,
    Col3 DATETIME
)

2. CTE聚合+排名关联方案 #

用两个CTE拆分逻辑：一个聚合分组内的非空值，另一个生成排名，最后关联合并结果：

WITH GroupAgg AS (
    -- 聚合每个PersonID下各列的非空值（MAX自动忽略NULL）
    SELECT
        PersonID,
        MAX(Col1) AS Agg_Col1,
        MAX(Col2) AS Agg_Col2,
        MAX(Col3) AS Agg_Col3
    FROM #FakeTable
    GROUP BY PersonID
),
RankedRows AS (
    -- 生成按KeyFieldCNT降序的排名
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY KeyFieldCNT DESC) AS RowNum
    FROM #FakeTable
)
-- 关联替换NULL值
SELECT
    r.PersonID,
    r.KeyFieldCNT,
    ISNULL(r.Col1, ga.Agg_Col1) AS Col1,
    ISNULL(r.Col2, ga.Agg_Col2) AS Col2,
    ISNULL(r.Col3, ga.Agg_Col3) AS Col3
FROM RankedRows r
JOIN GroupAgg ga ON r.PersonID = ga.PersonID
WHERE r.RowNum = 1

关键细节说明 #

• 用MAX()聚合：MAX函数自动忽略NULL，返回分组内该列的非空值；如果有多个非空值，会返回排序后的最大值，完全满足“只要存在非空就取到”的需求。
• 用ISNULL()替换：排名第一的行某列若为NULL，就用分组聚合的非空值替代，否则保留原行数据。
• 扩展列只需在GroupAgg中添加对应列的MAX聚合，再在最终SELECT中用ISNULL关联即可。

简化写法（适合列少的场景） #

如果不想用CTE，可直接将聚合逻辑嵌入关联子查询：

SELECT
    r.PersonID,
    r.KeyFieldCNT,
    ISNULL(r.Col1, (SELECT MAX(Col1) FROM #FakeTable WHERE PersonID = r.PersonID)) AS Col1,
    ISNULL(r.Col2, (SELECT MAX(Col2) FROM #FakeTable WHERE PersonID = r.PersonID)) AS Col2,
    ISNULL(r.Col3, (SELECT MAX(Col3) FROM #FakeTable WHERE PersonID = r.PersonID)) AS Col3
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY KeyFieldCNT DESC) AS RowNum
    FROM #FakeTable
) r
WHERE r.RowNum = 1

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