MySQL如何将COUNT(*)结果显示为0而非隐藏行？含指定SQL优化需求

1. 怎么让MySQL里COUNT(*)的结果显示为0，而不是直接隐藏无数据的行？ #

其实核心逻辑很简单：你得先有一个包含所有要统计的分组维度的完整清单（比如你要统计的所有技能名、分类标签等），然后基于这个清单去原表中统计每个维度对应的记录数——而不是直接从原表GROUP BY，毕竟原表没有对应维度的行时，GROUP BY根本不会生成那一行。

给你个通用的例子：假设你要统计类别A、B、C的记录数，哪怕某个类别没数据也要显示0：

SELECT 
    category_list.category,
    COUNT(t.id) AS count
FROM (
    -- 先构造出所有要统计的维度，做成虚拟表
    SELECT 'A' AS category UNION ALL 
    SELECT 'B' AS category UNION ALL 
    SELECT 'C' AS category
) AS category_list
LEFT JOIN your_table t ON t.category = category_list.category
GROUP BY category_list.category;

这里用LEFT JOIN保证每个维度都会出现在结果里，注意要用COUNT(t.id)而不是COUNT(*)，不然会把虚拟表的空行也算进去，结果就变成1而不是0了。

如果不能用JOIN（比如你第二个问题的限制），也可以用子查询的方式实现：

SELECT 
    category_list.category,
    (SELECT COUNT(id) FROM your_table WHERE category = category_list.category) AS count
FROM (
    SELECT 'A' AS category UNION ALL 
    SELECT 'B' AS category UNION ALL 
    SELECT 'C' AS category
) AS category_list;

2. 针对你的SQL需求，实现显示p2 | 0（仅用SELECT，不用JOIN） #

你的原查询只返回p1，是因为GROUP BY只会从符合WHERE条件的行里分组，p2没有符合certified >10的行，自然不会生成对应的结果行。要解决这个问题，我们可以先构造出包含p1和p2的虚拟列表，然后对每个名称用子查询统计符合条件的员工数：

SELECT 
    skill_names.name,
    (SELECT COUNT(empNumber) FROM SKILL WHERE certified > 10 AND name = skill_names.name) AS employee_count
FROM (
    SELECT 'p1' AS name UNION ALL SELECT 'p2' AS name
) AS skill_names;

这个语句的逻辑拆解下：

1. 先用SELECT 'p1' UNION ALL SELECT 'p2'生成包含两个技能名的虚拟表skill_names
2. 对虚拟表中的每个名称，执行子查询统计SKILL表中符合certified >10且名称匹配的员工数量
3. 这样就算p2没有符合条件的员工，也会返回p2 | 0的结果

要是你觉得UNION ALL的写法不够直观，也可以用UNION ALL直接拼两个查询，可读性也不错：

SELECT 
    'p1' AS name,
    (SELECT COUNT(empNumber) FROM SKILL WHERE certified >10 AND name='p1') AS employee_count
UNION ALL
SELECT 
    'p2' AS name,
    (SELECT COUNT(empNumber) FROM SKILL WHERE certified >10 AND name='p2') AS employee_count;

这两种方式都只用到了SELECT语句，完全没用到JOIN，刚好满足你的要求。

内容的提问来源于stack exchange，提问作者Caramelia Bachmann