MySQL中不使用UNION ALL统计多列yes/no数量的最优实现方案

• 你当前的UNION ALL写法逻辑正确，能得到符合预期的结果，但不属于最佳实践：
  • 会对tb_count表执行两次全表扫描，数据量较大时IO开销是最优写法的2倍，性能差
  • 统计逻辑重复编写，后续新增统计列或者修改判断条件时需要同时修改两处代码，可维护性低容易出错

核心思路是先构造包含yes/no两个值的常量行，再关联原表做一次条件聚合，仅需扫描一次原表即可得到结果，代码更简洁可维护性更高。

MySQL通用版本（兼容所有版本） #

SELECT 
  v.value,
  SUM(CASE WHEN t.col1 = v.value THEN 1 ELSE 0 END) AS col1,
  SUM(CASE WHEN t.col2 = v.value THEN 1 ELSE 0 END) AS col2,
  SUM(CASE WHEN t.col3 = v.value THEN 1 ELSE 0 END) AS col3,
  SUM(CASE WHEN t.col4 = v.value THEN 1 ELSE 0 END) AS col4
FROM (
  SELECT 'yes' AS value 
  UNION ALL 
  SELECT 'no' AS value
) v
CROSS JOIN tb_count t
GROUP BY v.value
ORDER BY v.value DESC;

MySQL简化写法（利用MySQL布尔值隐式转换特性） #

MySQL中条件判断结果为真时返回1，假返回0，因此可以省略CASE语句直接求和，代码更短：

SELECT 
  v.value,
  SUM(t.col1 = v.value) AS col1,
  SUM(t.col2 = v.value) AS col2,
  SUM(t.col3 = v.value) AS col3,
  SUM(t.col4 = v.value) AS col4
FROM (
  SELECT 'yes' AS value 
  UNION ALL 
  SELECT 'no' AS value
) v
CROSS JOIN tb_count t
GROUP BY v.value
ORDER BY v.value DESC;

MySQL 8.0+ 更简洁写法 #

可以用VALUES语句构造常量行，无需写UNION ALL：

SELECT 
  v.value,
  SUM(t.col1 = v.value) AS col1,
  SUM(t.col2 = v.value) AS col2,
  SUM(t.col3 = v.value) AS col3,
  SUM(t.col4 = v.value) AS col4
FROM (VALUES ROW('yes'), ROW('no')) v(value)
CROSS JOIN tb_count t
GROUP BY v.value
ORDER BY v.value DESC;

优先选择上面的单次全表扫描方案，相比你原来的写法有两个明显优势：

1. 性能更好，仅扫描一次原表，IO开销减半
2. 可维护性更高，新增统计列仅需新增一行聚合逻辑，无需修改多处代码

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