如何无需子查询筛选无指定联系方式的唯一客户ID？

问题背景 #

我们有一张contact_options表，记录客户与他们的联系方式类型，核心对应关系如下：

• 电话（外键ID: 1）
• SMS（外键ID: 2）
• 邮件（外键ID: 3）

已知筛选拥有SMS或邮件联系方式的唯一客户ID的SQL语句是：

SELECT DISTINCT customer_id FROM contact_options WHERE contact_option_type_id IN (2,3)

现在需要实现反向需求：获取完全没有电话联系方式的唯一客户ID列表，且要求不能使用子查询（实际场景中存在80+种不同联系方式类型）。

可行方案：GROUP BY + HAVING 分组过滤 #

利用分组后聚合判断的方式，可以完美满足需求，无需子查询。这里提供两种常用写法：

写法1：使用SUM聚合判断

SELECT customer_id
FROM contact_options
GROUP BY customer_id
HAVING SUM(CASE WHEN contact_option_type_id = 1 THEN 1 ELSE 0 END) = 0;

写法2：使用MAX聚合判断

SELECT customer_id
FROM contact_options
GROUP BY customer_id
HAVING MAX(CASE WHEN contact_option_type_id = 1 THEN 1 ELSE 0 END) = 0;

逻辑解释 #

两种写法的核心思路一致：

1. GROUP BY customer_id：将表中所有记录按客户ID分组，每个客户对应一个独立分组。
2. CASE表达式+聚合函数：
   • 对每个分组内的记录，判断是否是电话类型（ID=1）：如果是则标记为1，否则标记为0。
   • 用SUM求和：如果结果为0，说明该客户的所有联系方式里没有任何电话类型记录。
   • 用MAX取最大值：如果结果为0，说明该客户分组里没有出现过标记为1的电话记录（只要有一条电话记录，MAX结果就会是1）。

适配实际多类型场景 #

因为实际存在80+种联系方式，这个方案的扩展性很强：

• 如果需要排除多种联系方式（比如同时排除电话和ID=5的类型），只需修改CASE里的条件：

SELECT customer_id
FROM contact_options
GROUP BY customer_id
HAVING SUM(CASE WHEN contact_option_type_id IN (1,5) THEN 1 ELSE 0 END) = 0;

• 这种写法不需要枚举所有允许的联系方式类型，只需要指定要排除的类型即可，非常适合多类型场景。

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