SQL查询问题：如何筛选所有持有人均属于指定城市的账户

现有一张账户持有人关联表，包含3个字段：

• customer ID：客户ID
• account number：账户编号
• City：客户居住城市，仅和客户ID关联，与账户无关

规则说明 #

• 单个客户可持有多个账户
• 单个账户可对应多个持有人（即多个客户ID）
• 需求：查询所有账户持有人均属于指定城市（示例指定为纽约）的账户，只要账户有1位持有人不属于目标城市就不返回。

示例数据 #

按照需求查询所有持有人均在纽约的账户，仅账户12345符合条件。

我们需要筛选出不存在任何非目标城市持有人的账户，有两种常用实现方案，性能表现因数据库索引情况略有差异：

1. 分组聚合方案：按账户分组后，校验该组内所有城市都等于目标城市
2. 不存在匹配方案：直接筛选没有关联到非目标城市持有人的账户

方案1：GROUP BY + HAVING 实现 #

兼容性最好，适配所有主流数据库：

SELECT ACCOUNT
FROM 你的表名
GROUP BY ACCOUNT
HAVING COUNT(CASE WHEN CITY != 'New York' THEN 1 END) = 0;

方案2：NOT EXISTS 实现 #

数据量较大、ACCOUNT字段有索引时性能更优：

SELECT DISTINCT t1.ACCOUNT
FROM 你的表名 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM 你的表名 t2
    WHERE t2.ACCOUNT = t1.ACCOUNT
      AND t2.CITY != 'New York'
);

如果需要更换目标城市，只需要把语句里的'New York'替换成对应的城市名即可。

内容的提问来源于stack exchange，提问作者שחר בר-כהן