SQL Server中合并Class列含NULL与非NULL值的记录

问题背景 #

我手头有这样一份数据集：

我想要实现两个目标：

• 对那些**同一组内（按id、ProteinID、Gene_Name分组）**Class列同时存在NULL和非NULL值的记录，合并后保留非NULL的Class值
• 完全保留那些组内Class全为NULL的行（比如id=10013的这条）

尝试过用COALESCE，但它会把所有NULL行都清掉，没找到合适的方法，求指点。

解决方案 #

这个需求用窗口函数就能轻松搞定，核心思路是先给每组标记是否存在非NULL的Class值，然后针对性地取值。下面提供两种通用的写法，适配大多数主流SQL数据库（PostgreSQL、MySQL 8.0+、SQL Server等）：

写法一：清晰版（带注释）

WITH grouped_data AS (
    SELECT 
        id,
        ProteinID,
        Gene_Name,
        Class,
        -- 标记当前分组是否存在非NULL的Class
        MAX(CASE WHEN Class IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY id, ProteinID, Gene_Name) AS has_non_null
    FROM your_table -- 替换成你的表名
)
SELECT DISTINCT
    id,
    ProteinID,
    Gene_Name,
    -- 分组有非NULL值就取该值，否则保留原NULL
    CASE 
        WHEN has_non_null = 1 THEN MAX(Class) OVER (PARTITION BY id, ProteinID, Gene_Name)
        ELSE Class
    END AS Class
FROM grouped_data;

写法二：简洁版

SELECT DISTINCT
    id,
    ProteinID,
    Gene_Name,
    -- 利用MAX忽略NULL的特性，分组有非NULL值就取它，否则保留原NULL
    COALESCE(MAX(Class) OVER (PARTITION BY id, ProteinID, Gene_Name), Class) AS Class
FROM your_table; -- 替换成你的表名

关键逻辑说明 #

1. 分组依据：PARTITION BY id, ProteinID, Gene_Name确保我们只处理那些属于同一组的重复记录，避免误合并不相关的行。
2. MAX函数的妙用：MAX(Class)会自动忽略NULL值，所以如果组内有非NULL的Class，它会直接返回那个有效值；如果全是NULL，它也会返回NULL。
3. COALESCE的正确用法：这里用它来判断，如果分组的MAX结果不是NULL就用它，否则保留原行的NULL（也就是组内全为NULL的情况）。
4. DISTINCT去重：因为同一组的记录处理后结果完全一致，用DISTINCT可以得到你想要的无重复的输出。

这样运行后就能得到你期望的结果啦：

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