收集数据、计数并返回字典列表，即使数据不存在

Question

假设我有这样一个mysql表

| id | type        | sub_type | customer    |
|  1 | animal      | cat      | John        |
|  2 | animal      | dog      | Marry       |
|  3 | animal      | fish     | Marry       |
|  3 | animal      | bird     | John        |

我要做的是按客户收集数据，按子类型计算行数。动物类型有4个亚型（cat、dog、fish、bird），约翰有两个亚型（cat、bird），而Marry也有两个亚型（dog、fish）。假设我想得到John的结果，应该是这样的

[
  {name='cat', count=1},
  {name='dog', count=0},
  {name='fish', count=0},
  {name='bird', count=1}
]

当我想得到一个关于Marry的结果时，应该是这样的

[
  {name='cat', count=0},
  {name='dog', count=1},
  {name='fish', count=1},
  {name='bird', count=0}
]

所以，不在数据库中的子类型应该是返回值为0的count。假设我想得到Matthew的结果。因为没有Matthew的数据，结果应该是这样的

 [
  {name='cat', count=0},
  {name='dog', count=0},
  {name='fish', count=0},
  {name='bird', count=0}
]

我通常使用setdefault()来生成结果。我的代码可能是这样的

tmp = dict()
for row in queryset:
   tmp.setdefault(row.customer, dict(cat=0, dog=0, fish=0, bird=0))
   if row.sub_type == 'cat':
      tmp[row.customer][row.sub_type] += 1

然而，我想知道是否有其他方法或更优雅的方法来做到这一点

Answer 1

假设您有一个名为“people”的表，其中包含包含条目的字段“name”

name
    
John
Mary
Mathew

上面提到的那张桌子叫“宠物”

您可以使用以下查询为每个人构建结果集

select
  A.name as customer,
  (select count(*) from pets where customer=A.name and sub_type='cat') as cat,
  (select count(*) from pets where customer=A.name and sub_type='dog') as dog,
  (select count(*) from pets where customer=A.name and sub_type='fish') as fish,
  (select count(*) from pets where customer=A.name and sub_type='bird') as bird
from people A

结果如下

customer    cat     dog     fish    bird
John        1       0       0       1
Marry       0       1       1       0
Mathew      0       0       0       0

添加一个额外的where子句并过滤我的名字或提供 一次汇总所有结果