如何从employee表中查询同时匹配organization_id和city_id的公共country_id

问题原因 #

你当前使用OR条件查询是取满足organization_id=1和city_id=3两个条件的行的并集，返回的是这两类行所有country_id的去重结果，自然会得到3和5，不符合找「同时存在于两类行中」的country_id的需求。

解决方案 #

方案1：单表聚合查询（性能最优，全数据库兼容）

仅扫描一次表，通过分组判断每个country_id是否同时匹配两类条件：

SELECT country_id
FROM employee
WHERE organization_id = 1 OR city_id = 3
GROUP BY country_id
HAVING COUNT(DISTINCT CASE 
    WHEN organization_id = 1 THEN 1 
    WHEN city_id = 3 THEN 2 
END) = 2;

逻辑说明：分组后如果某个country_id同时存在于匹配organization_id=1和city_id=3的行中，CASE语句会返回2种不同的值，COUNT(DISTINCT)结果就是2，刚好筛选出目标值。

方案2：交集查询（逻辑最直观）

如果使用PostgreSQL、Oracle、SQL Server等支持INTERSECT关键字的数据库，可以直接写：

SELECT country_id FROM employee WHERE organization_id = 1
INTERSECT
SELECT country_id FROM employee WHERE city_id = 3;

如果是不支持INTERSECT的MySQL，可以改用IN子查询实现：

SELECT DISTINCT country_id 
FROM employee 
WHERE organization_id = 1 
AND country_id IN (SELECT country_id FROM employee WHERE city_id = 3);

以上两种方案对样例数据都会仅返回5，符合预期。

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