给定字符串列表如何查询MySQL表中各元素统计数含不存在项0值

问题原因 #

你原来的查询拿不到计数为0的bbb条目，本质是WHERE name IN ('aaa','bbb','ccc')只会筛选出业务表中已存在的匹配行，不存在的bbb根本不会进入后续分组统计的环节，自然没有对应的结果。
要实现需求单条SQL完全可以做到，核心思路是先构造包含所有待查询名称的临时结果集，再和业务表左关联后统计。

实现方案 #

假设你的业务表名为score_table（注意table是MySQL关键字，不要直接用作表名，替换成你实际的表名即可）。

1. MySQL 8.0+ 版本（简洁写法）

支持VALUES行构造器，可以快速生成临时结果集：

SELECT
  t.target_name AS `name`,
  COUNT(s.`name`) AS `count`
FROM
  (VALUES ROW('aaa'), ROW('bbb'), ROW('ccc')) AS t(target_name)
LEFT JOIN `score_table` s
  ON t.target_name = s.`name`
GROUP BY t.target_name;

2. MySQL 5.x 低版本兼容写法

用UNION ALL手动构造临时结果集：

SELECT
  t.target_name AS `name`,
  COUNT(s.`name`) AS `count`
FROM
  (
    SELECT 'aaa' AS target_name
    UNION ALL SELECT 'bbb'
    UNION ALL SELECT 'ccc'
  ) AS t
LEFT JOIN `score_table` s
  ON t.target_name = s.`name`
GROUP BY t.target_name;

注意点 #

统计时要用COUNT(s.name)而不是COUNT(*)：左关联后无匹配的行对应的s.name为NULL，COUNT会自动忽略NULL值，刚好得到计数0；如果用COUNT(*)会把左关联生成的空行也计入，得到错误结果1。
如果待查询的列表很长，也可以先把所有待查询名称批量插入到一张临时表中，再用临时表替换上面的t子查询做关联统计，写法会更简洁。

内容的提问来源于stack exchange，提问作者suihe dolood