sql 语法、计数、分组依据和添加在语句中

Question

我有一个这样的 sql 语句(使用 mysql 5.5.15):

select global_id, count(id) as group_count
from globals_lists
group by global_id
order by group_count desc;

并希望修改它以支持具有如下 in 语句的子集:

select global_id, count(id) as group_count
from globals_lists
where global_id in (3,4,5,6)
group by global_id
order by group_count desc;

我会得到这样的结果:

global_id   group_count
3           15 
5           12

但想要

global_id   group_count
3           15 
5           12
4           0
6           0

这可能吗？我尝试了几个 have 条款，但似乎无法让它发挥作用。

Answer 1

看起来有点尴尬，但怎么样？

select IDs.ID, ifnull(count(globals_lists.global_id),0) as group_count
from (select 3 as ID
             UNION select 4
             UNION select 5
             UNION select 6) as IDs
left join globals_lists on IDs.ID = globals_lists.global_id
group by IDs.ID
order by group_count desc;