如何编写SQL语句筛选x、y、z的子集，排除仅含z的集合

需求明确：保留所有编号分组，只要该分组的取值集合不是仅包含z——换句话说，分组里至少存在x或y（可以同时包含z）。

方法1：窗口函数实现（推荐，一次扫描表） #

SELECT 编号, 取值
FROM (
    SELECT 
        编号, 
        取值,
        -- 标记当前编号分组是否包含x或y
        MAX(CASE WHEN 取值 IN ('x', 'y') THEN 1 ELSE 0 END) OVER (PARTITION BY 编号) AS has_valid
    FROM 你的表名
) filtered
WHERE has_valid = 1;

逻辑说明：子查询中用窗口函数MAX() OVER (PARTITION BY 编号)对每个编号分组进行判断——只要分组里有任意一行是x或y，has_valid就会被标记为1；外层筛选出标记为1的所有行，直接排除仅含z的分组。

方法2：分组子查询关联 #

SELECT t.编号, t.取值
FROM 你的表名 t
JOIN (
    SELECT 编号
    FROM 你的表名
    GROUP BY 编号
    -- 筛选出分组中存在x或y的编号
    HAVING SUM(CASE WHEN 取值 IN ('x', 'y') THEN 1 ELSE 0 END) > 0
) valid_groups ON t.编号 = valid_groups.编号;

逻辑说明：先通过子查询分组筛选出符合条件的编号（分组里至少有一个x或y），再将原表与这些编号关联，得到所有符合要求的行。

效果验证 #

针对你的示例数据，两种方法都会排除编号3的所有行，保留编号1和编号2的全部记录，与期望输出完全一致。

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