mysql 多条件统计

第一种写法

	select	
				count(IF(t2.work_order_status=102 or t2.work_order_status=5,1,null)) as totalComWorkOrderNum, 
				count(IF(t2.work_order_status=9,1,null)) as toBeTakingWorkOrderNum, 
				count(IF(t2.work_order_status=10,1,null)) as toBeSignedWorkOrderNum, 
				count(IF(t2.work_order_status=101,1,null)) as toBeHxWorkOrderNum, 
				count(if(STR_TO_DATE(t1.reservation_date,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d') and t2.work_order_status=9,1,null)) as todayWorkOrderNum, 
				count(if(STR_TO_DATE(t1.reservation_date,'%Y-%m-%d')=DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 DAY) and t2.work_order_status=9,1,null)) as tomorrowWorkOrderNum,
				0 as toBeAppointedWorkOrderNum
				from p_user_dispatch_make_info t1
				INNER JOIN p_user_service_work_order_info t2
				ON t1.work_order_id=t2.id
				where t1.is_deleted=0
				and t1.is_enable=1
				and t2.is_deleted=0 
				and t2.is_enable=1

第二种写法

select	
				count(IF(t2.work_order_status=102 or t2.work_order_status=5,true,null)) as totalComWorkOrderNum, 
				count(IF(t2.work_order_status=9,true,null)) as toBeTakingWorkOrderNum, 
				count(IF(t2.work_order_status=10,true,null)) as toBeSignedWorkOrderNum, 
				count(IF(t2.work_order_status=101,true,null)) as toBeHxWorkOrderNum, 
				count(if(STR_TO_DATE(t1.reservation_date,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d') and t2.work_order_status=9,true,null)) as todayWorkOrderNum, 
				count(if(STR_TO_DATE(t1.reservation_date,'%Y-%m-%d')=DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 DAY) and t2.work_order_status=9,true,null)) as tomorrowWorkOrderNum,
				0 as toBeAppointedWorkOrderNum
				from p_user_dispatch_make_info t1
				INNER JOIN p_user_service_work_order_info t2
				ON t1.work_order_id=t2.id
				where t1.is_deleted=0
				and t1.is_enable=1
				and t2.is_deleted=0 
				and t2.is_enable=1

第三种写法

select	
				sum(IF(t2.work_order_status=102 or t2.work_order_status=5,1,0)) as totalComWorkOrderNum, 
				sum(IF(t2.work_order_status=9,1,0)) as toBeTakingWorkOrderNum, 
				sum(IF(t2.work_order_status=10,1,0)) as toBeSignedWorkOrderNum, 
				sum(IF(t2.work_order_status=101,1,0)) as toBeHxWorkOrderNum, 
				sum(if(STR_TO_DATE(t1.reservation_date,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d') and t2.work_order_status=9,1,0)) as todayWorkOrderNum, 
				sum(if(STR_TO_DATE(t1.reservation_date,'%Y-%m-%d')=DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 DAY) and t2.work_order_status=9,1,0)) as tomorrowWorkOrderNum,
				0 as toBeAppointedWorkOrderNum
				from p_user_dispatch_make_info t1
				INNER JOIN p_user_service_work_order_info t2
				ON t1.work_order_id=t2.id
				where t1.is_deleted=0
				and t1.is_enable=1
				and t2.is_deleted=0 
				and t2.is_enable=1

第四种写法

	select	
				count(CASE WHEN t2.work_order_status=102 or t2.work_order_status=5 THEN 1 ELSE null END) as totalComWorkOrderNum, 
				count(CASE WHEN t2.work_order_status=9 THEN 1 else null END) as toBeTakingWorkOrderNum, 
				count(CASE WHEN t2.work_order_status=10 THEN 1 ELSE null END) as toBeSignedWorkOrderNum, 
				count(CASE WHEN t2.work_order_status=101 THEN 1 ELSE NULL END) as toBeHxWorkOrderNum, 
				count(CASE WHEN STR_TO_DATE(t1.reservation_date,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d') and t2.work_order_status=9 THEN 1 ELSE null END) as todayWorkOrderNum, 
				count(CASE WHEN STR_TO_DATE(t1.reservation_date,'%Y-%m-%d')=DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 DAY) and t2.work_order_status=9 THEN 1 ELSE NULL END) as tomorrowWorkOrderNum,
				0 as toBeAppointedWorkOrderNum
				from p_user_dispatch_make_info t1
				INNER JOIN p_user_service_work_order_info t2
				ON t1.work_order_id=t2.id
				where t1.is_deleted=0
				and t1.is_enable=1
				and t2.is_deleted=0 
				and t2.is_enable=1

第五种写法

	select	
				count(CASE WHEN t2.work_order_status=102 or t2.work_order_status=5 THEN true ELSE null END) as totalComWorkOrderNum, 
				count(CASE WHEN t2.work_order_status=9 THEN true else null END) as toBeTakingWorkOrderNum, 
				count(CASE WHEN t2.work_order_status=10 THEN true ELSE null END) as toBeSignedWorkOrderNum, 
				count(CASE WHEN t2.work_order_status=101 THEN true ELSE NULL END) as toBeHxWorkOrderNum, 
				count(CASE WHEN STR_TO_DATE(t1.reservation_date,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d') and t2.work_order_status=9 THEN true ELSE null END) as todayWorkOrderNum, 
				count(CASE WHEN STR_TO_DATE(t1.reservation_date,'%Y-%m-%d')=DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 DAY) and t2.work_order_status=9 THEN true ELSE NULL END) as tomorrowWorkOrderNum,
				0 as toBeAppointedWorkOrderNum
				from p_user_dispatch_make_info t1
				INNER JOIN p_user_service_work_order_info t2
				ON t1.work_order_id=t2.id
				where t1.is_deleted=0
				and t1.is_enable=1
				and t2.is_deleted=0 
				and t2.is_enable=1