mysql - 如何在一个 SQL 查询中获取某一列的多个计数？

Question

我有一个类似的架构:

PetOwner:
owner_id | first_name | last_name
---------------------------------
1        | john       | smith
2        | jane       | doe
3        | jack       | jones    

Pets:
pet_id | owner_id | name | type
-------------------------------
1      | 1        | a    | dog
2      | 1        | b    | dog
3      | 1        | c    | dog
4      | 1        | d    | cat
5      | 1        | e    | cat
6      | 1        | f    | fish
7      | 2        | g    | dog
8      | 2        | h    | dog
9      | 2        | i    | fish
10     | 3        | j    | dog

我想编写一个查询来提供:

first_name | last_name | dog_count | cat_count | fish_count
-----------------------------------------------------------
john       | smith     | 3         | 2         | 1
jane       | doe       | 2         | 0         | 1
jack       | jones     | 1         | 0         | 0

有一组已知的宠物类型，因此不需要为任意类型创建一列。此外，任何没有特定类型宠物的主人都应该获得该宠物类型的 0 计数。

我已经尝试过如下查询:

SELECT first_name, last_name, owner_id, type, COUNT(*)
FROM Pets, PetsOwner
GROUP_BY owner_id, type

产生:

first_name | last_name | owner_id | type | COUNT(*)
---------------------------------------------------
john       | smith     | 1        | dog  | 3
john       | smith     | 1        | cat  | 2
john       | smith     | 1        | fish | 1
jane       | doe       | 2        | dog  | 2
jane       | doe       | 2        | fish | 1
jack       | jones     | 3        | dog  | 1

但这需要迭代结果集以获得我正在寻找的输出，显然这是 SQL 需要避免的事情。此外，它也不为表中缺少的宠物类型提供默认的 0 值。

是否可以通过一个 SQL 查询来完成此操作？如果没有，是否有一个查询比我找到的查询更好(即更容易使用)？

如果有区别的话，它是一个 MySQL 数据库。

感谢您的帮助。

Answer 1

使用 SUM 而不是 COUNT:

SELECT o.first_name, o.last_name, 
       SUM(CASE WHEN p.type = 'cat' THEN 1 ELSE 0 END) as cat_count,
       SUM(CASE WHEN p.type = 'dog' THEN 1 ELSE 0 END) as dog_count,
       SUM(CASE WHEN p.type = 'fish' THEN 1 ELSE 0 END) as fish_count
FROM PetOwners o
JOIN Pets p ON o.id = p.owner_id
GROUP BY o.id

如果您想包含根本没有任何宠物的主人，请将 JOIN 更改为 LEFT JOIN。