如何高效实现同一列多组OR条件的AND组合查询？

表结构与示例数据 #

以下是客户、分组及客户分组关联表的创建语句与示例数据：

CREATE TABLE customer(id) AS VALUES (0),(1),(2),(3);
CREATE TABLE groups(id)   AS VALUES (1),(3),(5),(6);
CREATE TABLE customers_to_groups(customer_id, group_id) AS
  VALUES (0, 1)--customer 0 is in group (5 OR 6) AND (1 OR 3)
        ,(0, 5)--customer 0 is in group (5 OR 6) AND (1 OR 3)
        ,(1, 1)
        ,(1, 90)
        ,(2, 1)
        ,(3, 3)--customer 3 is in group (5 OR 6) AND (1 OR 3)
        ,(3, 5)--customer 3 is in group (5 OR 6) AND (1 OR 3)
        ,(3, 90);

需求说明 #

需要筛选出同时满足以下两个条件的客户：

• 至少属于一个第一组分组（ID为5或6）
• 至少属于一个第二组分组（ID为1或3）

根据示例数据，符合条件的客户ID为0和3。需要注意的是，直接使用WHERE group_id IN (5,6) AND group_id IN (1,3)无法实现需求——单个分组ID不可能同时属于两个不同的集合，逻辑上不成立。

现有可行方案 #

目前已经实现的可行方案通过自连接关联两次客户分组表，分别匹配两组分组条件，最后去重得到结果：

SELECT DISTINCT c.id
FROM customer c
INNER JOIN customers_to_groups at1 ON c.id = at1.customer_id
INNER JOIN customers_to_groups at2 ON c.id = at2.customer_id
WHERE at1.group_id IN (5, 6)
  AND at2.group_id IN (1, 3);

预期结果 #

性能更优的实现方式 #

现有方案需要两次自连接并去重，数据量大时会产生大量中间数据，性能存在优化空间。以下两种方案性能更优：

方案1：GROUP BY + 条件聚合 #

通过一次扫描客户分组表，利用分组聚合判断客户是否同时满足两个条件，无需自连接和去重：

SELECT customer_id AS id
FROM customers_to_groups
WHERE group_id IN (1, 3, 5, 6) -- 提前过滤无关分组，减少处理数据量
GROUP BY customer_id
HAVING 
  -- 验证客户至少属于一个第一组分组
  SUM(CASE WHEN group_id IN (5, 6) THEN 1 ELSE 0 END) > 0
  -- 验证客户至少属于一个第二组分组
  AND SUM(CASE WHEN group_id IN (1, 3) THEN 1 ELSE 0 END) > 0;

如果使用PostgreSQL，还可以用更简洁的BOOL_OR函数实现：

SELECT customer_id AS id
FROM customers_to_groups
WHERE group_id IN (1, 3, 5, 6)
GROUP BY customer_id
HAVING 
  BOOL_OR(group_id IN (5, 6))
  AND BOOL_OR(group_id IN (1, 3));

方案2：EXISTS子查询 #

利用EXISTS的短路特性，分别检查客户是否存在于两组分组中，搭配customers_to_groups(customer_id, group_id)索引可以大幅提升查询效率：

SELECT c.id
FROM customer c
WHERE 
  EXISTS (
    SELECT 1 
    FROM customers_to_groups ctg 
    WHERE ctg.customer_id = c.id AND ctg.group_id IN (5, 6)
  )
  AND EXISTS (
    SELECT 1 
    FROM customers_to_groups ctg 
    WHERE ctg.customer_id = c.id AND ctg.group_id IN (1, 3)
  );

性能对比 #

• 现有方案：自连接会生成笛卡尔积（一个客户若属于多个符合条件的分组，会产生多条重复记录），后续需要DISTINCT去重，数据量大时性能开销较高。
• 方案1：仅扫描一次客户分组表，通过聚合直接筛选，无中间重复数据，性能更稳定。
• 方案2：EXISTS会在找到第一条符合条件的记录后停止扫描，搭配索引时查询速度极快，适合大表场景。

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