查找匹配多个行值的外键 [英] Find foreign key matching multiple row values

问题描述

我有这样的桌子设计

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

，并希望有一个查询返回属于部门a和b的所有person_id.我认为这可能与

and would like to have a query return all of the person_id's that belong to both department a and b. I figured it would be something along the lines of

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

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

but can't quite figure it out. Does anyone know how I can do this?

我想我可以执行以下操作.但这似乎是一种黑客.

I figured out I can do the following. But it seems like a hack.

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

推荐答案

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

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

Note: The DISTINCT is only needed if a person can be a member of the same department more than once.

这篇关于查找匹配多个行值的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！