笔记-----统计错误实例

错误语句：

select row_number() over (),2019,2019,'','','',440605,'南海区',440600,'佛山市',
a33.zj,a33.frblsl,
cast(a33.frblsl*100/(a33.frblsl+a33.zrrblsl) as decimal(8,2)) as frblslzb,
a33.zrrblsl,
cast(a33.zrrblsl*100/(a33.frblsl+a33.zrrblsl) as decimal(8,2)) as zrrblslzb
from 
(select a22.year,a22.month,a22.day,a22.zj,
sum(case when a22.sbdxlx_type_name='企业' then a22.zhckbllzb else 0 end)  as frblsl,
sum(case when a22.sbdxlx_type_name='个人' then a22.zhckbllzb else 0 end)  as zrrblsl
from 
(select c1.year,c1.month,c1.day,a11.zj,c2.sbdxlx_type_name,count(1) as zhckbllzb
from 
ioc_nh_jsc.M_SS_ZW_dtsxbl c
left join 
(select * from m_gwd_sj2 where length(hour) in (1,2)) c1
on c.sjwd=c1.rid
left join 
M_WD_sbdxlx c2
on c.sbdxlxwd=c2.sbdxlx_type_id

left join 

(select d.zj,c1.slbm from 
ioc_nh_jsc.M_SS_ZW_dtsxbl c1
left join 
(select * from m_gwd_sj2 where length(hour) in (1,2)) c2
on c1.sjwd=c2.rid
left join 
(select distinct slbm,concat(substr(slbm,1,position('镇' in slbm)-1),'镇') as zj from ioc_nh_jsc.M_SS_ZW_dtsxbl where slbm like '%综合窗口%') d
on c1.slbm=d.slbm where length(d.zj)>3) a11
on
c.slbm=a11.slbm
group by c1.year,c1.month,c1.day,a11.zj,c2.sbdxlx_type_name 
having a11.zj is not null and c2.sbdxlx_type_name!='投资项目') a22
group by a22.year,a22.month,a22.day,a22.zj) a33;

正确语句：

select row_number() over (),a33.year,a33.year,a33.month,a33.day,'',440605,'南海区',440600,'佛山市',
a33.zj,a33.frblsl,
cast(a33.frblsl*100/(a33.frblsl+a33.zrrblsl) as decimal(8,2)) as frblslzb,
a33.zrrblsl,
cast(a33.zrrblsl*100/(a33.frblsl+a33.zrrblsl) as decimal(8,2)) as zrrblslzb
from 
(select a22.year,a22.month,a22.day,a22.zj,
sum(case when a22.sbdxlx_type_name='企业' then a22.bll else 0 end)  as frblsl,
sum(case when a22.sbdxlx_type_name='个人' then a22.bll else 0 end)  as zrrblsl
from 

(select p.year,p.month,p.day,p.zj,p.sbdxlx_type_name,count(*) as bll
from 
(select c1.year,c1.month,c1.day,d.zj,c.slbm,c2.sbdxlx_type_name
from 
ioc_nh_jsc.M_SS_ZW_dtsxbl c
left join 
(select * from m_gwd_sj2 where length(hour) in (1,2)) c1
on c.sjwd=c1.rid
left join 
M_WD_sbdxlx c2
on c.sbdxlxwd=c2.sbdxlx_type_id
left join 
(select distinct slbm,concat(substr(slbm,1,position('镇' in slbm)-1),'镇') as zj from ioc_nh_jsc.M_SS_ZW_dtsxbl where slbm like '%综合窗口%') d
on c.slbm=d.slbm where length(d.zj)>3) p
group by p.year,p.month,p.day,p.zj,p.sbdxlx_type_name
having p.zj is not null and p.sbdxlx_type_name!='投资项目') a22
group by a22.year,a22.month,a22.day,a22.zj) a33;