如何在GROUP BY分组中选取空值数量最少的行？

解决方案 #

咱们先把需求和示例数据理清楚：你需要按id分组，每个组里挑选空值（NULL）数量最少的行；如果有多个行空值数量相同，只保留其中一行即可（比如去掉和row2完全重复的row4）。

先把你的示例数据整理成清晰的表格：

最通用的方法是用窗口函数，几乎所有现代SQL数据库（MySQL 8.0+、PostgreSQL、SQL Server等）都支持。思路分三步：

1. 给每行计算空值的数量
2. 按id分组，给每行按「空值数从小到大、行号从小到大」排名
3. 只取每个组里排名第一的行

直接上可执行的代码：

WITH ranked_rows AS (
    SELECT 
        *,
        -- 计算当前行的空值总数：每个字段为NULL则加1
        (firstname IS NULL) + (middlename IS NULL) + (lastname IS NULL) AS null_count,
        -- 按id分组排序：先按空值数升序，再按row_number升序（确保留最早出现的行）
        ROW_NUMBER() OVER (
            PARTITION BY id 
            ORDER BY null_count ASC, row_number ASC
        ) AS rn
    FROM your_table_name -- 替换成你的实际表名
)
SELECT row_number, id, firstname, middlename, lastname
FROM ranked_rows
WHERE rn = 1;

代码细节解释：

• null_count：利用SQL里布尔值转数字的特性（IS NULL返回true/false，会被自动转成1/0），快速统计每行的空值数量。比如row0的null_count是1，row1是0；row2和row4的null_count都是0。
• ROW_NUMBER()窗口函数：给每个id组内的行排序，空值少的排前面；空值数相同的话，row_number更小的排前面，这样就会保留最早出现的那一行（比如row2比row4先出现，就留row2）。
• 最后筛选rn=1，就能得到每个组里符合要求的唯一行。

执行后得到的结果：

完全符合你的需求！如果你的数据库版本比较老（比如MySQL 5.x不支持CTE），可以把CTE改成子查询形式：

SELECT row_number, id, firstname, middlename, lastname
FROM (
    SELECT 
        *,
        (firstname IS NULL) + (middlename IS NULL) + (lastname IS NULL) AS null_count,
        ROW_NUMBER() OVER (
            PARTITION BY id 
            ORDER BY null_count ASC, row_number ASC
        ) AS rn
    FROM your_table_name
) AS sub_query
WHERE rn = 1;

另外，如果你的数据里有大量完全重复的行，也可以先去重再排名，只需要在第一步加个DISTINCT：

WITH deduplicated AS (
    SELECT DISTINCT row_number, id, firstname, middlename, lastname
    FROM your_table_name
),
ranked_rows AS (
    SELECT 
        *,
        (firstname IS NULL) + (middlename IS NULL) + (lastname IS NULL) AS null_count,
        ROW_NUMBER() OVER (
            PARTITION BY id 
            ORDER BY null_count ASC, row_number ASC
        ) AS rn
    FROM deduplicated
)
SELECT row_number, id, firstname, middlename, lastname
FROM ranked_rows
WHERE rn = 1;

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