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

这可能吗？我尝试了一些有条款，但似乎无法使其发挥作用。

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;

Answer 2

您可以添加包含所有所需值的表。要么是内联的，如下所示，要么是在临时表中。然后您可以使用left join来匹配行数。

select  lst.nr
,       count(gl.id) as group_count
from    (
        select 3 as nr
        union all select 4
        union all select 5
        union all select 6
        ) lst
left join 
        globals_lists gl
on      lst.nr = gl.global_id
group by 
        lst.nr
order by 
        count(gl.id) desc

Answer 3

使用 COUNT(*) 而不是 COUNT(id)，这样即使没有，它也会返回一个计数（假设 4 & ）。确实存在 6 个：

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