mysql group by不满足返回0,count（*）在MySQL中使用group by时不返回0

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