count（*）在MySQL中使用group by时不返回0 [英] count(*) Does not return 0 when using group by in MySQL

问题描述

我有以下查询

  SELECT info，count（*）as info_cnt 
 FROM T 
 WHERE uid = 1 and info IN（'a'，'b'，'c'，'d'）
 GROUP BY info 
   
 
它返回
  + ---------- + ------- + 
 | info_cnt |信息| 
 + ---------- + -------- + 
 | 334 | a | 
 | 2 | b | 
 | 1400 | d | 
 + ---------- + -------- + 
  
 
 $ b b 
如何编写查询，使其返回 c 作为 0 的计数。
解决方案
您可以创建一个临时表：
  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 
  
 
I have the following query 
SELECT info, count(*) as info_cnt 
FROM T
WHERE uid = 1 and info IN ('a', 'b','c', 'd')
GROUP BY info
It returns 
+----------+--------+
| info_cnt | info   |
+----------+--------+
|      334 | a      |
|        2 | b      | 
|     1400 | d      |
+----------+--------+
How can I write the query so that it returns the count of c as 0.
解决方案
You could create a temporary table:
CREATE TEMPORARY TABLE info_values (v CHAR(1));
INSERT INTO info_values VALUES ('a'), ('b'), ('c'), ('d');
And then do a 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


                        
这篇关于count（*）在MySQL中使用group by时不返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！