mysql - 选择其外部行仅匹配单个值的行

假设我有两张桌子——people和pets——每个人可能有不止一只宠物：
people：

+-----------+-------+
| person_id | name  |
+-----------+-------+
|         1 | Bob   |
|         2 | John  |
|         3 | Pete  |
|         4 | Waldo |
+-----------+-------+

pets：

+--------+-----------+--------+
| pet_id | person_id | animal |
+--------+-----------+--------+
|      1 |         1 | dog    |
|      2 |         1 | dog    |
|      3 |         1 | cat    |
|      4 |         2 | cat    |
|      5 |         3 | dog    |
|      6 |         3 | tiger  |
|      7 |         3 | tiger  |
|      8 |         4 | tiger  |
|      9 |         4 | tiger  |
|     10 |         4 | tiger  |
+--------+-----------+--------+

我正试着挑选那些只有tiger作为宠物的人。显然，唯一符合这个标准的是Waldo，因为Pete也有一个dog。。。但是我在写这个查询时遇到了一些问题。
最明显的情况是select people.person_id, people.name from people join pets on people.person_id = pets.person_id where pets.animal = "tiger"，但这会返回Pete和Waldo。
如果有一个条款如pets.animal ONLY = "tiger"会有帮助，但据我所知，这是不存在的。
如何编写查询？

最佳答案

 select people.person_id, people.name
   from people
   join pets on people.person_id = pets.person_id
  where pets.animal = "tiger"
    AND people.person_id NOT IN (select person_id from pets where animal != 'tiger');

关于mysql - 选择其外部行仅匹配单个值的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/32036849/