MySQL 列出实体并在连接表上应用 AND 过滤器

Question

有两张表:Person、HouseHouse 有一个到 Person 的 FK，名为 person_idHouse有一个字段叫city

有没有办法列出在 city_a 和 city_b 都有房子的所有人？这应该排除仅在其中一个城市拥有房屋的人，但包括在两个城市以及其他城市拥有房屋的人。

这是我当前的查询:

SELECT person.* 
FROM Person person 
JOIN House house ON house.person_id = person.id 
WHERE house.city IN ("city_a", "city_b");

但是，此查询仅返回在 city_a 或 city_b 拥有房屋的人员列表，因此不满足 AND 条件。

Answer 1

一个简单的方法使用 exists 。 。 。两次:

select p.*
from person p
where exists (select 1 from house h where h.person_id = p.id and h.city = 'city_a') and
      exists (select 1 from house h where h.person_id = p.id and h.city = 'city_b') ;

如果您只想要此人的 ID，那么 group by 和 having 会很方便:

select h.person_id
from house h
where h.city in ('city_a', 'city_b')
group by h.person_id
having count(distinct h.city) = 2