SQL Server条件聚合：获取各列最高Rank的非NULL值

SQL解决方案 #

方法1：窗口函数+条件聚合（通用兼容方案） #

通过ROW_NUMBER()为每个列的非NULL行按Rank降序编号，再通过条件聚合提取每个列的目标值，适用于绝大多数SQL数据库（MySQL、PostgreSQL、SQL Server等）。

WITH ranked_data AS (
    SELECT 
        ID,
        Rank,
        Column_A,
        Column_B,
        Column_C,
        Column_D,
        Column_E,
        -- 为每个列的非NULL行按Rank降序生成编号，NULL行编号靠后
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CASE WHEN Column_A IS NOT NULL THEN Rank ELSE 0 END DESC) AS rn_A,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CASE WHEN Column_B IS NOT NULL THEN Rank ELSE 0 END DESC) AS rn_B,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CASE WHEN Column_C IS NOT NULL THEN Rank ELSE 0 END DESC) AS rn_C,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CASE WHEN Column_D IS NOT NULL THEN Rank ELSE 0 END DESC) AS rn_D,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CASE WHEN Column_E IS NOT NULL THEN Rank ELSE 0 END DESC) AS rn_E
    FROM your_table
)
SELECT 
    ID,
    MAX(Rank) AS Rank, -- 取当前ID的最高Rank
    MAX(CASE WHEN rn_A = 1 THEN Column_A END) AS Column_A,
    MAX(CASE WHEN rn_B = 1 THEN Column_B END) AS Column_B,
    MAX(CASE WHEN rn_C = 1 THEN Column_C END) AS Column_C,
    MAX(CASE WHEN rn_D = 1 THEN Column_D END) AS Column_D,
    MAX(CASE WHEN rn_E = 1 THEN Column_E END) AS Column_E
FROM ranked_data
GROUP BY ID;

方法2：FIRST_VALUE窗口函数（简洁写法） #

利用FIRST_VALUE()提取排序后的第一个非NULL值，再取每个ID的最高Rank行作为结果行，逻辑更直观。

WITH ordered_data AS (
    SELECT 
        ID,
        Rank,
        -- 按"非NULL行优先、Rank降序"排序，取第一个值即为目标值
        FIRST_VALUE(Column_A) OVER (PARTITION BY ID ORDER BY CASE WHEN Column_A IS NOT NULL THEN Rank ELSE -1 END DESC) AS top_A,
        FIRST_VALUE(Column_B) OVER (PARTITION BY ID ORDER BY CASE WHEN Column_B IS NOT NULL THEN Rank ELSE -1 END DESC) AS top_B,
        FIRST_VALUE(Column_C) OVER (PARTITION BY ID ORDER BY CASE WHEN Column_C IS NOT NULL THEN Rank ELSE -1 END DESC) AS top_C,
        FIRST_VALUE(Column_D) OVER (PARTITION BY ID ORDER BY CASE WHEN Column_D IS NOT NULL THEN Rank ELSE -1 END DESC) AS top_D,
        FIRST_VALUE(Column_E) OVER (PARTITION BY ID ORDER BY CASE WHEN Column_E IS NOT NULL THEN Rank ELSE -1 END DESC) AS top_E,
        -- 标记当前ID的最高Rank行
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Rank DESC) AS rn
    FROM your_table
)
SELECT 
    ID,
    Rank,
    top_A AS Column_A,
    top_B AS Column_B,
    top_C AS Column_C,
    top_D AS Column_D,
    top_E AS Column_E
FROM ordered_data
WHERE rn = 1;

方法3：PostgreSQL专属优化（DISTINCT ON+子查询） #

PostgreSQL支持DISTINCT ON语法，结合子查询可以高效实现需求，逻辑简单易懂。

-- 先获取每个ID的最高Rank
WITH top_rank_per_id AS (
    SELECT DISTINCT ON (ID) ID, Rank 
    FROM your_table 
    ORDER BY ID, Rank DESC
)
SELECT 
    tr.ID,
    tr.Rank,
    -- 子查询取当前ID下该列最高Rank的非NULL值
    (SELECT Column_A FROM your_table t WHERE t.ID = tr.ID AND Column_A IS NOT NULL ORDER BY Rank DESC LIMIT 1) AS Column_A,
    (SELECT Column_B FROM your_table t WHERE t.ID = tr.ID AND Column_B IS NOT NULL ORDER BY Rank DESC LIMIT 1) AS Column_B,
    (SELECT Column_C FROM your_table t WHERE t.ID = tr.ID AND Column_C IS NOT NULL ORDER BY Rank DESC LIMIT 1) AS Column_C,
    (SELECT Column_D FROM your_table t WHERE t.ID = tr.ID AND Column_D IS NOT NULL ORDER BY Rank DESC LIMIT 1) AS Column_D,
    (SELECT Column_E FROM your_table t WHERE t.ID = tr.ID AND Column_E IS NOT NULL ORDER BY Rank DESC LIMIT 1) AS Column_E
FROM top_rank_per_id tr;

注意事项 #

• 当列数超过50时，可通过脚本批量生成对应列的SQL逻辑，避免手动编写重复代码
• 确保Rank在每个ID内唯一，若存在重复Rank值，需调整窗口函数的排序规则（例如添加其他列作为排序依据）

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