我有三个表,我想用另一表列中的组对每一行进行计数。
问题是没有找到记录时,group的计数将不返回任何内容。
所以我想为找不到记录的每个组添加空值。

这是查询:

select monster.monster_name,count(*) as count
from monster right join monster_ability
on monster.monster_id= monster_ability.monster_id  where
isnull(monster_ability.used)
group by monster.monster_id


这是小提琴:fiddle

我希望结果看起来像这样:

| monster_name | count |
|--------------|-------|
|         kora |     1 |
|      lowdowu |     3 |
|      ngjengeh|   null|
|       lortyu |     1 |
|     foh du fy|   null|

最佳答案

当count为0时,使用case when获得null:

select
    m.monster_name,
    case when count(a.ability_id) = 0 then null else count(a.ability_id) end as `count`
from monster m
left join monster_ability ma on m.monster_id = ma.monster_id and ma.used is null
left join ability a on ma.ability_id = a.ability_id
group by m.monster_id


SQLFiddle demo在这里。

关于mysql - sql选择带组的计数,如果未找到结果,则添加空值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/42804263/

10-16 09:54