FULL JOIN查询问题：如何获取全部CMS值并统计缺陷状态

场景说明 #

初始数据表 data_table：

期望得到的结果表：

遇到的问题 #

我原本写的SQL代码尝试通过两个子查询分别统计true和false的缺陷数，再用FULL JOIN关联，但当我选择false_table.CMS时，结果丢失了CMS=1（这个值只存在于true_table中），代码如下：

SELECT DISTINCT false_table.CMS, true_table.true_defects, false_table.false_defects
FROM(
 SELECT DISTINCT CMS, COUNT(*) AS true_defects
 FROM data_table
 WHERE defect_status = 'true'
 GROUP BY CMS
) as true_table
FULL JOIN(
 SELECT DISTINCT CMS, COUNT(*) AS false_defects
 FROM data_table
 WHERE defect_status = 'false'
 GROUP BY CMS
) as false_table
ON true_table.CMS = false_table.CMS

解决方法 #

方案1：修复FULL JOIN的CMS列选择问题

问题出在你只取了false_table.CMS，而FULL JOIN后，当某个CMS只在true_table存在时，false_table.CMS会是NULL，所以需要用COALESCE函数来取两个表中不为空的CMS值，同时还要用COALESCE处理统计数的NULL，把它转为0：

SELECT 
  COALESCE(true_table.CMS, false_table.CMS) AS CMS,
  COALESCE(true_table.true_defects, 0) AS true_defects,
  COALESCE(false_table.false_defects, 0) AS false_defects
FROM(
 SELECT CMS, COUNT(*) AS true_defects
 FROM data_table
 WHERE defect_status = 'true'
 GROUP BY CMS
) as true_table
FULL JOIN(
 SELECT CMS, COUNT(*) AS false_defects
 FROM data_table
 WHERE defect_status = 'false'
 GROUP BY CMS
) as false_table
ON true_table.CMS = false_table.CMS

另外注意子查询里的DISTINCT是多余的，因为GROUP BY CMS已经会返回唯一的CMS值了，可以去掉。

方案2：用条件聚合简化代码（更推荐）

其实完全不需要拆分两个子查询再关联，用条件聚合可以一步完成统计，代码更简洁高效：

SELECT 
  CMS,
  COUNT(CASE WHEN defect_status = 'true' THEN 1 END) AS true_defects,
  COUNT(CASE WHEN defect_status = 'false' THEN 1 END) AS false_defects
FROM data_table
GROUP BY CMS

这里CASE语句会在条件满足时返回1，否则返回NULL，而COUNT函数会忽略NULL值，正好实现统计对应状态的数量；对于没有对应状态的CMS，自然会统计出0。

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