mysql在同一个表中统计复杂条件的数量

示例表及需求

需求，在一个表中根据条件统计数量，且这些条件之间有交叉。如下图所示。如果只是针对不同的type进行数量统计的话，我们使用group by就可以达到目的。但是我们看下面的条件，不仅对type有一个区分，而且在type中还要统计其他的条件sex。

原始想法Union

统计量多不怕，只要我们分开统计，多搞几个临时表就解决了，如下图所示：

select sum(女剑修数量) as '女剑修数量',sum(女剑修宗门数)as '女剑修宗门数',sum(剑修数量)as '剑修数量',
			 sum(剑修所在宗门数)as '剑修所在宗门数',sum(武夫数量)as '武夫数量',sum(武夫所在宗门数)as '武夫所在宗门数'
from(
	select count(1) as '女剑修数量',count(distinct school) as '女剑修宗门数',
					0 as '剑修数量',0 as '剑修所在宗门数',0 as '武夫数量',0 as '武夫所在宗门数' 
	from wgd_test where type = '剑修' and sex = '女'
	union
	select 0 as '女剑修数量',0 as '女剑修宗门数',count(1) as '剑修数量',
					count(distinct school) as '剑修所在宗门数',0 as '武夫数量',0 as '武夫所在宗门数' 
	from wgd_test where type = '剑修'
	union
	select 0 as '女剑修数量',0 as '女剑修宗门数',0 as '剑修数量',0 as '剑修所在宗门数',
					count(1) as '武夫数量',count(distinct school) as '武夫所在宗门数' 
	from wgd_test where type = '武夫'
)tmp

这样来写，功能肯定是能实现的，但是缺点也很明显，临时表多，需要union操作，代码长，万一中间表规模较大，又耗时又耗空间。

进阶想法case when

select 
SUM(CASE WHEN type = '剑修' AND sex = '女' THEN 1 ELSE 0 END) as '女剑修数量',
COUNT(DISTINCT (CASE WHEN type = '剑修' AND sex = '女' THEN school ELSE null END)) as '女剑修宗门数',
SUM(CASE WHEN type = '剑修' THEN 1 ELSE 0 END) as '剑修数量',
COUNT(DISTINCT (CASE WHEN type = '剑修' THEN school ELSE null END)) as '剑修所在宗门数',
SUM(CASE WHEN type = '武夫' THEN 1 ELSE 0 END) as '武夫数量',
COUNT(DISTINCT (CASE WHEN type = '武夫' THEN school ELSE null END)) as '武夫所在宗门数'
from wgd_test

使用该写法最明显的一个特点就是代码简介，只需要一个临时表，而且在case when中可以对临时表进行各种条件判断。

陷阱优化

上述的case when方法中有一个陷阱就是，sum函数对于中间表记录为空的情况会返回null，如果代码中没有对返回字段进行判断的话，会有空指针异常。那这种属于数据库的事情，最好就是在数据库中得到解决，办法就是COALESCE函数。将SUM函数外面再打个包

select 
COALESCE(SUM(CASE WHEN type = '剑修' AND sex = '女' THEN 1 ELSE 0 END),0) as '女剑修数量',
COUNT(DISTINCT (CASE WHEN type = '剑修' AND sex = '女' THEN school ELSE null END)) as '女剑修宗门数',
COALESCE(SUM(CASE WHEN type = '剑修' THEN 1 ELSE 0 END),0) as '剑修数量',
COUNT(DISTINCT (CASE WHEN type = '剑修' THEN school ELSE null END)) as '剑修所在宗门数',
COALESCE(SUM(CASE WHEN type = '武夫' THEN 1 ELSE 0 END),0) as '武夫数量',
COUNT(DISTINCT (CASE WHEN type = '武夫' THEN school ELSE null END)) as '武夫所在宗门数'
from wgd_test