SQL查询需求：筛选distinct category,sub_category并按规则过滤记录

现有一张表，包含以下两列：

• category：带非空约束（not null），该字段始终有值
• sub_category：默认值为null，属于可选字段

需要查询该表中去重后的category和sub_category组合，同时满足以下规则：

• 若某个category下同时存在非空的sub_category和null的sub_category，排除其中sub_category为null的记录
• 若某个category仅存在category+<null>的组合，则保留这条记录

示例数据 #

category    sub_category  
girl_toys        fluffy
girl_toys        dolls
drawing          <null> -- 保留，因为该分类下只有这条null记录
paining          red_color
paining          <null> -- 排除，因为该分类下存在非null的sub_category

期望结果 #

category    sub_category  
girl_toys        fluffy
girl_toys        dolls
drawing          <null> -- 保留，因为drawing分类只有这条null记录
paining          red_color -- 排除null记录，因为该分类下存在非null的sub_category

解决方案 #

方法1：使用窗口函数 #

通过窗口函数统计每个分类下非空子分类的数量，再基于该数量筛选符合要求的记录：

WITH category_stats AS (
    SELECT 
        category,
        sub_category,
        COUNT(CASE WHEN sub_category IS NOT NULL THEN 1 END) OVER (PARTITION BY category) AS non_null_sub_count
    FROM your_table
)
SELECT DISTINCT category, sub_category
FROM category_stats
WHERE 
    (non_null_sub_count > 0 AND sub_category IS NOT NULL)
    OR (non_null_sub_count = 0 AND sub_category IS NULL);

方法2：分组关联筛选 #

先分组统计每个分类是否存在非空子分类，再关联原表进行筛选：

SELECT DISTINCT t.category, t.sub_category
FROM your_table t
JOIN (
    SELECT 
        category,
        MAX(CASE WHEN sub_category IS NOT NULL THEN 1 ELSE 0 END) AS has_non_null_sub
    FROM your_table
    GROUP BY category
) c ON t.category = c.category
WHERE 
    (c.has_non_null_sub = 1 AND t.sub_category IS NOT NULL)
    OR (c.has_non_null_sub = 0 AND t.sub_category IS NULL);

两种方法逻辑一致：先判断每个分类下是否存在非空子分类，存在则只保留非空记录；不存在则保留唯一的null记录。

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