查找匹配多个行值的外键

Question

我有这样的桌子设计

person_id   |   department
1           |   a
1           |   b
2           |   a
2           |   c
3           |   b
3           |   a

并希望通过查询返回属于部门a和b的所有person_id。我认为这可能与

select person_id from table group by person_id having ....

但不能完全弄清楚。有人知道我该怎么做吗？

编辑：我想我可以执行以下操作。但这似乎是一种hack。

select person_id from table where department='A' or department='B' 
     group by person_id having count(1) > 1

Answer 1

SELECT
  person_id
FROM
  yourTable
WHERE
     department = 'a'
  OR department = 'b'
GROUP BY
  person_id
HAVING
  COUNT(DISTINCT department) = 2

注意：仅当一个人可以多次成为同一部门的成员时，才需要使用DISTINCT。