MySql子查询有多个结果的查询方法

查询背景:

需要从案件上报的记录表中, 统计出案件上报数量 、 案件已经受理的数量 、 案件已经废止的数量 、 案件已经结案的数量。

UNION  会去掉重复的行

UNION ALL  不会去掉重复的行

先用COUNT统计出每个数据 ， 然后再用SUM相加得出数量

SELECT SUM(t1.shangBao) AS '上报', SUM(t1.shouLi) AS '受理', SUM(t1.feiZhi) AS '废止', SUM(t1.banJie) AS '办结' FROM (
	SELECT COUNT(uid) AS shangBao , 0 AS shouLi, 0 AS feiZhi, 0 AS banJie FROM case_report WHERE is_delete = 0  UNION ALL 	
	SELECT 0  , COUNT(uid) , 0 , 0 FROM case_report WHERE is_delete = 0 AND case_is_accept = '是' UNION ALL 
	SELECT 0 , 0 , COUNT(uid) , 0 FROM case_report WHERE is_delete = 0 AND case_is_abolish = '是' UNION ALL 	
	SELECT 0 , 0 , 0 , COUNT(uid)FROM case_report WHERE is_delete = 0 AND case_status = '已办结'		
) t1

第二种查询方式(最后我使用的第二种)

SELECT 
    SUM( CASE WHEN is_delete = 0 THEN 1 ELSE 0 END ) AS '上报',
    SUM( CASE WHEN is_delete = 0 AND case_is_accept = '是' THEN 1 ELSE 0 END  ) AS '受理',
    SUM( CASE WHEN is_delete = 0 AND case_is_abolish = '是' THEN 1 ELSE 0 END  ) AS '废止',
    SUM( CASE WHEN is_delete = 0 AND case_status = '已办结' THEN 1 ELSE 0 END  ) AS '办结'
FROM
    case_report