报表不同数据组合sql

1、显示结果：

 2、sql语句：

 

select 
 proviceNumberCount
,provice2GNumberCount
,provice3GNumberCount
,proviceNumberSuccessCount
,provice2GNumberSuccessCount
,provice3GNumberSuccessCount
,proviceNumberMoneyCount
,provice2GNumberMoneyCount
,provice3GNumberMoneyCount
from
(
	select 
	 count(sp_id) as proviceNumberCount
	,province
	from tb_entity_dingdanmingxi ddmx
	where type in(1)
	and flag=1
	and status in(5,6)
	group by province
) proviceNumberCountTable left outer join
(
	select
 	 count(ddmx.sp_id) as provice2GNumberCount
	,ddmx.province
	from tb_entity_dingdanmingxi ddmx left outer join tb_entity_number hm
	on  ddmx.sp_id=hm.id
	where ddmx.type in(1)
	and ddmx.flag=1
	and ddmx.status in(5,6)
	and hm.wl_type in (0)
	group by ddmx.province
) provice2GNumberCountTable on proviceNumberCountTable.province=provice2GNumberCountTable.province left outer join
(
	select
 	 count(ddmx.sp_id) as provice3GNumberCount
	,ddmx.province
	from tb_entity_dingdanmingxi ddmx left outer join tb_entity_number hm
	on  ddmx.sp_id=hm.id
	where ddmx.type in(1)
	and ddmx.flag=1
	and ddmx.status in(5,6)
	and hm.wl_type in (1)
	group by ddmx.province
)provice3GNumberCountTable on proviceNumberCountTable.province=provice3GNumberCountTable.province left outer join
(
	select 
	 count(sp_id) as proviceNumberSuccessCount
	,ddmx.province
	from tb_entity_dingdanmingxi ddmx
	where type in(1)
	and flag=1
	and status in(6)
	group by province
) proviceNumberSuccessCountTable on proviceNumberCountTable.province=proviceNumberSuccessCountTable.province left outer join
(
	select
 	 count(ddmx.sp_id) as provice2GNumberSuccessCount
	,ddmx.province
	from tb_entity_dingdanmingxi ddmx left outer join tb_entity_number hm
	on  ddmx.sp_id=hm.id
	where ddmx.type in(1)
	and ddmx.flag=1
	and ddmx.status in(6)
	and hm.wl_type in (0)
	group by ddmx.province
) provice2GNumberCountSuccessTable on proviceNumberCountTable.province=provice2GNumberCountSuccessTable.province left outer join
(
	select
 	 count(ddmx.sp_id) as provice3GNumberSuccessCount
	,ddmx.province
	from tb_entity_dingdanmingxi ddmx left outer join tb_entity_number hm
	on  ddmx.sp_id=hm.id
	where ddmx.type in(1)
	and ddmx.flag=1
	and ddmx.status in(6)
	and hm.wl_type in (1)
	group by ddmx.province
)provice3GNumberCountSuccessTable on proviceNumberCountTable.province=provice3GNumberCountSuccessTable.province left outer join
(
	select 
	 sum(cast(jg as int)) as proviceNumberMoneyCount
	,ddmx.province
	from tb_entity_dingdanmingxi ddmx
	where type in(1)
	and flag=1
	and status in(6)
	group by province
) proviceNumberMoneyCountTable on proviceNumberCountTable.province=proviceNumberMoneyCountTable.province left outer join
(
	select
 	 sum(cast(ddmx.jg as int)) as provice2GNumberMoneyCount
	,ddmx.province
	from tb_entity_dingdanmingxi ddmx left outer join tb_entity_number hm
	on  ddmx.sp_id=hm.id
	where ddmx.type in(1)
	and ddmx.flag=1
	and ddmx.status in(6)
	and hm.wl_type in (0)
	group by ddmx.province
) provice2GNumberMoneyCountTable on proviceNumberCountTable.province=provice2GNumberMoneyCountTable.province left outer join
(
	select
 	 sum(cast(ddmx.jg as int)) as provice3GNumberMoneyCount
	,ddmx.province
	from tb_entity_dingdanmingxi ddmx left outer join tb_entity_number hm
	on  ddmx.sp_id=hm.id
	where ddmx.type in(1)
	and ddmx.flag=1
	and ddmx.status in(6)
	and hm.wl_type in (1)
	group by ddmx.province
) provice3GNumberMoneyCountTable on proviceNumberCountTable.province=provice3GNumberMoneyCountTable.province