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