mysql - 将MySQL的COUNT与两个表一起使用

我有以下名为tbl_pet_owners的MySQL表：

+--------+----------+--------+
| name   | pet      | city   |
+========+==========+========+
| jane   | cat      | Boston |
+--------+----------+--------+
| jane   | dog      | Boston |
+--------+----------+--------+
| jack   | cat      | Boston |
+--------+----------+--------+
| jim    | snake    | Boston |
+--------+----------+--------+
| jim    | goldfish | Boston |
+--------+----------+--------+
| joseph | cat      | NYC    |
+--------+----------+--------+

我想使用COUNT来获取每个城市的宠物数量，但是如果一个人拥有两个或更多相同类型的宠物，则这些宠物将被计为一个。宠物类型在另一个名为tbl_pet_types的MySQL表中列出：

+----------+---------+
| pet      | type    |
+==========+=========+
| cat      | mammal  |
+----------+---------+
| dog      | mammal  |
+----------+---------+
| snake    | reptile |
+----------+---------+
| goldfish | fish    |
+----------+---------+

因此，由于简的猫和狗属于同一类型，因此被视为一只宠物。
在此示例中，结果将是：

Boston 4
NYC    1

关于如何实现这一目标的任何提示？

最佳答案

可以对其进行优化，但可以：

select count(*), result.city from (
    select owners.city, types.type, owners.name
    from tbl_pet_owners owners
    left join tbl_pet_types types on owners.pet = types.pet group by owners.city, owners.name, types.type
) as result
group by result.city;