sql - 按每个项目至少有一个进行分组

Question

假设我有一个匹配 person_id 到 pet 的表。我有一份房主必须拥有的理想宠物 list (每种至少一只)，从下表中，我想看看谁符合要求。

这个列表当然是 (dog, cat, tiger)。人们肯定可以拥有不止一个，但这些是必不可少的(因此 person_id = 1 是唯一有效的)。

+---------+-----------+--------+
| home_id | person_id |  pet   |
+---------+-----------+--------+
|       1 |         1 | dog    |
|       2 |         1 | dog    |
|       3 |         1 | cat    |
|       4 |         1 | tiger  |
|       5 |         2 | dog    |
|       6 |         2 | cat    |
|       7 |         3 | <null> |
|       8 |         4 | tiger  |
|       9 |         4 | tiger  |
|      10 |         4 | tiger  |
+---------+-----------+--------+

我已经能够通过运行来检查谁有老虎或猫:

select person_id, pet 
from house 
group by person_id having pet in ('dog','cat','tiger'), 

但很明显，这给出了至少拥有其中一只宠物的 person_id——而不是全部。

Answer 1

这样做的一种方法是计算每个人有多少不同的宠物，并将其与不同宠物的总数进行比较(即加入):

SELECT person_id
FROM   (SELECT   person_id, COUNT(DISTINCT pet) AS dp
        FROM     pets
        GROUP BY person_id) a
JOIN   (SELECT COUNT(DISTINCT pet) AS dp FROM pets) b ON a.dp = b.dp

编辑:
如果只有一些宠物被认为是“理想的”，并且这个列表是预先知道的，则可以通过在 where 子句中引入此信息来大大简化查询:

SELECT   person_id
FROM     pets
WHERE    pet IN ('dog', 'cat', 'tiger')
GROUP BY person_id
HAVING   COUNT(DISTINCT pet) = 3