如何高效筛选同时含Column B为X和Y的Column_A唯一值

原始数据表 #

需求说明 #

获取所有唯一的Column_A值，要求该值对应的行中**同时存在Column_B等于'X'和'Column_B'等于'Y'**的记录，期望结果如下：

期望结果 #

尝试的SQL语句 #

SELECT DISTINCT COLUMN_A
FROM TABLE
INNER JOIN (
   SELECT DISTINCT COLUMN_A
   FROM TABLE
   WHERE COLUMN_B = 'X') SUBTABLE 
ON TABLE.COLUMN_A = SUBTABLE.COLUMN_A
WHERE TABLE.COLUMN_B = 'Y';

该语句可实现需求，但性能并非最优，需要更高效的解决方案。

高效解决方案 #

方案1：GROUP BY + HAVING（推荐）

通过分组后过滤，直接统计每个Column_A包含的目标值数量：

SELECT Column_A
FROM TABLE
WHERE Column_B IN ('X', 'Y')
GROUP BY Column_A
HAVING COUNT(DISTINCT Column_B) = 2;

这种方式只需扫描一次表，若表上有(Column_A, Column_B)复合索引，可直接利用索引完成统计，无需回表，避免了子查询和JOIN的额外开销。

方案2：双重EXISTS

通过两次存在性判断，确保Column_A同时关联X和Y的记录：

SELECT DISTINCT Column_A
FROM TABLE t1
WHERE EXISTS (
    SELECT 1 FROM TABLE t2 
    WHERE t2.Column_A = t1.Column_A AND t2.Column_B = 'X'
)
AND EXISTS (
    SELECT 1 FROM TABLE t3 
    WHERE t3.Column_A = t1.Column_A AND t3.Column_B = 'Y'
);

若表上有(Column_B, Column_A)复合索引或Column_A、Column_B的单独索引，EXISTS的半连接会快速定位匹配记录，逻辑清晰且性能优异。

性能优化提示 #

• 创建复合索引(Column_A, Column_B)，上述两种方案都能最大化利用该索引，显著提升查询速度。
• 方案1中GROUP BY本身已返回唯一的Column_A，无需额外添加DISTINCT；方案2若表中Column_A与Column_B的组合无重复，也可去掉DISTINCT。

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