MySQL查询求助：按机构统计角色数量结果异常

首先看一下你遇到的问题：原查询返回结果缺失了部分机构，还出现了错误的数值匹配，这主要是因为你的查询在表连接和条件处理上有几个关键问题：

1. 隐式连接导致笛卡尔积：你用了旧的逗号连接表的语法，而且WHERE条件只关联了主表a和b、d表，完全没关联c表（Agent的统计子查询）。这会让c表的所有数据和其他表做笛卡尔积，比如Client Relate本身没有Agent，但会错误匹配到其他机构的Agent计数，导致结果错误。
2. 内连接过滤了缺失角色的机构：当某个机构没有某个角色（比如IT Service没有Chief）时，内连接会直接把这个机构从结果中排除，因为对应的子查询（比如d表）没有该机构的记录。
3. NULLIF的用法错误：NULLIF(count(role),0)是多余的，因为count()函数本身不会返回0——没有匹配行时它会返回NULL，我们需要的是把NULL转换成0，应该用IFNULL或者COALESCE。

推荐解决方案：条件聚合（最简洁高效） #

MySQL中实现行转列统计，用条件聚合是最直接的方式，不需要多表连接，代码更简洁，性能也更好：

SELECT 
  institution,
  COUNT(CASE WHEN role = 'Responsable' THEN 1 END) AS Responsable,
  COUNT(CASE WHEN role = 'Agent' THEN 1 END) AS Agent,
  COUNT(CASE WHEN role = 'Chief' THEN 1 END) AS Chief
FROM services
WHERE month = '2019-04'
GROUP BY institution
ORDER BY institution ASC;

原理说明：

• CASE WHEN会给符合角色条件的行标记为1，不符合的标记为NULL；
• COUNT()函数会忽略NULL值，只统计非空的数量，没有匹配的角色自然就返回0；
• 整个查询只需要扫描一次表，比多表连接的效率高很多。

修复原有连接方式的版本 #

如果你更倾向于用子查询的方式，需要改成LEFT JOIN来保留所有机构，同时正确关联所有子表，并处理NULL为0：

SELECT 
  a.institution,
  IFNULL(b.Responsable, 0) AS Responsable,
  IFNULL(c.Agent, 0) AS Agent,
  IFNULL(d.Chief, 0) AS Chief
FROM (
  -- 获取所有符合条件的机构
  SELECT institution FROM services WHERE month = '2019-04' GROUP BY institution
) a
-- 左连接每个角色的统计子查询，确保机构不丢失
LEFT JOIN (
  SELECT institution, COUNT(role) AS Responsable 
  FROM services 
  WHERE month = '2019-04' AND role = 'Responsable' 
  GROUP BY institution
) b ON a.institution = b.institution
LEFT JOIN (
  SELECT institution, COUNT(role) AS Agent 
  FROM services 
  WHERE month = '2019-04' AND role = 'Agent' 
  GROUP BY institution
) c ON a.institution = c.institution
LEFT JOIN (
  SELECT institution, COUNT(role) AS Chief 
  FROM services 
  WHERE month = '2019-04' AND role = 'Chief' 
  GROUP BY institution
) d ON a.institution = d.institution
ORDER BY a.institution ASC;

这两个方案都能得到你期望的结果：

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