在MySQL中使用group by时，count(*)不返回0

Question

我有以下查询

SELECT info, count(*) as info_cnt 
FROM T
WHERE uid = 1 and info IN ('a', 'b','c', 'd')
GROUP BY info

它返回

+----------+--------+
| info_cnt | info   |
+----------+--------+
|      334 | a      |
|        2 | b      | 
|     1400 | d      |
+----------+--------+

如何编写查询，使其以0形式返回c的计数。

Answer 1

您可以创建一个临时表：

CREATE TEMPORARY TABLE info_values (v CHAR(1));
INSERT INTO info_values VALUES ('a'), ('b'), ('c'), ('d');

然后做一个LEFT JOIN

SELECT v, count(uid) as info_cnt 
FROM info_values
LEFT JOIN T ON uid = 1 AND T.info = info_values.v
GROUP BY v

Answer 2

对于编写查询的方式，不能返回零。如果表中有info值，则可以对其进行连接，也可以使用类似以下内容的子查询：

SELECT c.info, count(t1.info) as info_cnt 
FROM 
(
  select 'a' info
  union all
  select 'b'
  union all
  select 'c'
  union all
  select 'd'
) c
left join T t1
  on c.info = t1.info
  and t1.info IN ('a', 'b','c', 'd')
where uid = 1
GROUP BY c.info

请参阅SQL Fiddle with Demo

Answer 3

请试试这个：

SQLFIDDLE DEMO

select distinct t.info, coalesce(a.cnt,0) counts
from T
left join 
(select info, count(*) as cnt
 from T where
 uid = 1
 and info in ('a', 'b', 'c', 'd')
group by info)a
 on t.info = a.info
 group by t.info
;

| INFO | COUNTS |
-----------------
|    a |      2 |
|    b |      1 |
|    c |      0 |
|    d |      3 |