如何编写SQL查询按指定规则过滤含NULL值的表数据？

需求说明 #

给定数据表：

需要得到的结果：

规则：

• 若某x存在非NULL的y值，保留所有去重后的(x,y)行，剔除y为NULL的行
• 若某x对应的所有y均为NULL，仅保留一行(x, NULL)

建表与插入数据语句：

create table t (x text, y int);
insert into t
values ('a' , 1)
     , ('a' , null)
     , ('b' , 2)
     , ('b' , 3)
     , ('b' , 3)
     , ('b' , null)
     , ('b' , null)
     , ('c' , null)
     , ('c' , null);

解决方案 #

方法一：窗口函数+条件筛选（推荐，逻辑清晰） #

WITH x_group_info AS (
    SELECT 
        x, 
        y,
        -- 标记当前x分组是否存在非NULL的y值
        MAX(CASE WHEN y IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY x) AS has_non_null_y
    FROM t
)
SELECT DISTINCT x, y
FROM x_group_info
WHERE 
    (has_non_null_y = 1 AND y IS NOT NULL)
    OR (has_non_null_y = 0 AND y IS NULL);

方法二：分组聚合+UNION ALL（兼容性好，适配旧版数据库） #

-- 提取存在非NULL y的x分组，去重非NULL的y
SELECT DISTINCT x, y
FROM t
WHERE y IS NOT NULL
UNION ALL
-- 提取所有y均为NULL的x分组，仅保留一行
SELECT x, NULL
FROM t
GROUP BY x
HAVING COUNT(y) = 0;

说明 #

• 方法一利用窗口函数快速判断每个x分组的有效y存在情况，再针对性筛选并去重，支持PostgreSQL、MySQL 8.0+、SQL Server等主流数据库。
• 方法二拆分两种场景分别处理后合并结果，无需窗口函数，适配不支持窗口函数的旧版数据库。

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