/*******************************
--創(chuàng)建勞動保障公務(wù)案件統(tǒng)計(jì)表視圖16a1
*******************************/
create or replace view v_a1 as
select a.aj_id ajid,a.aj_flag aj_flag,a.dwxz dwxz,a.aj_lb ajlb,a.fkje fkje,a.qx qx,a.scajsj sj
			,(select sum(decode(d.mbid,'ZLGZ_HT',1,'ZLGZ_SX',1,'ZLGZ_GZ',1,'ZLGZ_QT',1,0)) from aj_ml d where a.aj_id = d.aj_id) zlgzjs
			,(select sum(decode(d.mbid,'XZCL_HT',1,'XZCL_SX',1,'XZCL_GZ',1,'XZCL_QT',1,0)) from aj_ml d where a.aj_id = d.aj_id) xzcljs
			,(select sum(decode(d.mbid,'XZCF_JG',1,'XZCF_MS',1,'XZCF_ZK',1,'XZCF_QT',1,'XZCF_FK_GZ',1,'XZCF_FK_HT',1,'XZCF_FK_QT',1,'XZCF_FK_SB',1,'XZCF_FK_TG',1,'XZCF_FK_WL',1,0)) from aj_ml d where a.aj_id = d.aj_id) xzcfjs
			,(select sum(decode(d.mbid,'XZCF_JG',1,0)) from aj_ml d where a.aj_id = d.aj_id) jgjs
			,(select sum(decode(d.mbid,'XZCF_FK_GZ',1,'XZCF_FK_HT',1,'XZCF_FK_QT',1,'XZCF_FK_SB',1,'XZCF_FK_TG',1,'XZCF_FK_WL',1,0)) from aj_ml d where a.aj_id = d.aj_id) fkjs
			,(select sum(decode(d.mbid,'XZCF_MS',1,'XZCF_ZK',1,'XZCF_QT',1,0)) from aj_ml d where a.aj_id = d.aj_id) qtjs
			,(select sum(decode(d.mbid,'QZZX_HT',1,'QZZX_SX',1,'QZZX_GZ',1,'QZZX_YG',1,'QZZX_QT',1,0)) from aj_ml d where a.aj_id = d.aj_id) qzzxjs
	from aj_djb a
 where a.aj_flag in ('03','04')
/

--勞動保障公務(wù)案件統(tǒng)計(jì)表第一行合計(jì)項(xiàng)
select 1 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'04',1,0)) col5,sum(decode(ajlb,'05',1,0)) col6
			,sum(decode(ajlb,'06',1,0)) col7,sum(decode(ajlb,'07',1,0)) col8,sum(decode(ajlb,'08',1,0)) col9
			,sum(decode(ajlb,'09',1,0)) col10,sum(decode(ajlb,'10',1,0)) col11,sum(decode(ajlb,'11',1,0)) col12
			,sum(decode(ajlb,'12',1,0)) col13,sum(decode(ajlb,'13',1,0)) col14,sum(decode(ajlb,'14',1,0)) col15
			,sum(decode(ajlb,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col16
			,sum(zlgzjs) col17,sum(xzcljs) col18,sum(xzcfjs) col19,sum(jgjs) col20,sum(fkjs) col21,sum(fkje) col22
			,sum(qtjs) col23,sum(qzzxjs) col24,'' col25,'' col26
	from v_a1
 where 1=1
/
--勞動保障公務(wù)案件統(tǒng)計(jì)表第2行分類統(tǒng)計(jì)
select 2 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'04',1,0)) col5,sum(decode(ajlb,'05',1,0)) col6
			,sum(decode(ajlb,'06',1,0)) col7,sum(decode(ajlb,'07',1,0)) col8,sum(decode(ajlb,'08',1,0)) col9
			,sum(decode(ajlb,'09',1,0)) col10,sum(decode(ajlb,'10',1,0)) col11,sum(decode(ajlb,'11',1,0)) col12
			,sum(decode(ajlb,'12',1,0)) col13,sum(decode(ajlb,'13',1,0)) col14,sum(decode(ajlb,'14',1,0)) col15
			,sum(decode(ajlb,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col16
			,sum(zlgzjs) col17,sum(xzcljs) col18,sum(xzcfjs) col19,sum(jgjs) col20,sum(fkjs) col21,sum(fkje) col22
			,sum(qtjs) col23,sum(qzzxjs) col24,'' col25,'' col26
	from v_a1
 where 1=1 and dwxz = '01'
/
--勞動保障公務(wù)案件統(tǒng)計(jì)表第3行分類統(tǒng)計(jì)
select 3 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'04',1,0)) col5,sum(decode(ajlb,'05',1,0)) col6
			,sum(decode(ajlb,'06',1,0)) col7,sum(decode(ajlb,'07',1,0)) col8,sum(decode(ajlb,'08',1,0)) col9
			,sum(decode(ajlb,'09',1,0)) col10,sum(decode(ajlb,'10',1,0)) col11,sum(decode(ajlb,'11',1,0)) col12
			,sum(decode(ajlb,'12',1,0)) col13,sum(decode(ajlb,'13',1,0)) col14,sum(decode(ajlb,'14',1,0)) col15
			,sum(decode(ajlb,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col16
			,sum(zlgzjs) col17,sum(xzcljs) col18,sum(xzcfjs) col19,sum(jgjs) col20,sum(fkjs) col21,sum(fkje) col22
			,sum(qtjs) col23,sum(qzzxjs) col24,'' col25,'' col26
	from v_a1
 where 1=1 and dwxz = '02'
/
--勞動保障公務(wù)案件統(tǒng)計(jì)表第4行分類統(tǒng)計(jì)
select 4 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'04',1,0)) col5,sum(decode(ajlb,'05',1,0)) col6
			,sum(decode(ajlb,'06',1,0)) col7,sum(decode(ajlb,'07',1,0)) col8,sum(decode(ajlb,'08',1,0)) col9
			,sum(decode(ajlb,'09',1,0)) col10,sum(decode(ajlb,'10',1,0)) col11,sum(decode(ajlb,'11',1,0)) col12
			,sum(decode(ajlb,'12',1,0)) col13,sum(decode(ajlb,'13',1,0)) col14,sum(decode(ajlb,'14',1,0)) col15
			,sum(decode(ajlb,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col16
			,sum(zlgzjs) col17,sum(xzcljs) col18,sum(xzcfjs) col19,sum(jgjs) col20,sum(fkjs) col21,sum(fkje) col22
			,sum(qtjs) col23,sum(qzzxjs) col24,'' col25,'' col26
	from v_a1
 where 1=1 and dwxz = '03'
/
--勞動保障公務(wù)案件統(tǒng)計(jì)表第5行分類統(tǒng)計(jì)
select 5 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'04',1,0)) col5,sum(decode(ajlb,'05',1,0)) col6
			,sum(decode(ajlb,'06',1,0)) col7,sum(decode(ajlb,'07',1,0)) col8,sum(decode(ajlb,'08',1,0)) col9
			,sum(decode(ajlb,'09',1,0)) col10,sum(decode(ajlb,'10',1,0)) col11,sum(decode(ajlb,'11',1,0)) col12
			,sum(decode(ajlb,'12',1,0)) col13,sum(decode(ajlb,'13',1,0)) col14,sum(decode(ajlb,'14',1,0)) col15
			,sum(decode(ajlb,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col16
			,sum(zlgzjs) col17,sum(xzcljs) col18,sum(xzcfjs) col19,sum(jgjs) col20,sum(fkjs) col21,sum(fkje) col22
			,sum(qtjs) col23,sum(qzzxjs) col24,'' col25,'' col26
	from v_a1
 where 1=1 and dwxz = '04'
/
--勞動保障公務(wù)案件統(tǒng)計(jì)表第6行分類統(tǒng)計(jì)
select 6 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'04',1,0)) col5,sum(decode(ajlb,'05',1,0)) col6
			,sum(decode(ajlb,'06',1,0)) col7,sum(decode(ajlb,'07',1,0)) col8,sum(decode(ajlb,'08',1,0)) col9
			,sum(decode(ajlb,'09',1,0)) col10,sum(decode(ajlb,'10',1,0)) col11,sum(decode(ajlb,'11',1,0)) col12
			,sum(decode(ajlb,'12',1,0)) col13,sum(decode(ajlb,'13',1,0)) col14,sum(decode(ajlb,'14',1,0)) col15
			,sum(decode(ajlb,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col16
			,sum(zlgzjs) col17,sum(xzcljs) col18,sum(xzcfjs) col19,sum(jgjs) col20,sum(fkjs) col21,sum(fkje) col22
			,sum(qtjs) col23,sum(qzzxjs) col24,'' col25,'' col26
	from v_a1
 where 1=1 and dwxz = '05'
/
--勞動保障公務(wù)案件統(tǒng)計(jì)表第7行分類統(tǒng)計(jì)
select 7 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'04',1,0)) col5,sum(decode(ajlb,'05',1,0)) col6
			,sum(decode(ajlb,'06',1,0)) col7,sum(decode(ajlb,'07',1,0)) col8,sum(decode(ajlb,'08',1,0)) col9
			,sum(decode(ajlb,'09',1,0)) col10,sum(decode(ajlb,'10',1,0)) col11,sum(decode(ajlb,'11',1,0)) col12
			,sum(decode(ajlb,'12',1,0)) col13,sum(decode(ajlb,'13',1,0)) col14,sum(decode(ajlb,'14',1,0)) col15
			,sum(decode(ajlb,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col16
			,sum(zlgzjs) col17,sum(xzcljs) col18,sum(xzcfjs) col19,sum(jgjs) col20,sum(fkjs) col21,sum(fkje) col22
			,sum(qtjs) col23,sum(qzzxjs) col24,'' col25,'' col26
	from v_a1
 where 1=1 and dwxz = '06'
/
--勞動保障公務(wù)案件統(tǒng)計(jì)表第8行分類統(tǒng)計(jì)
select 8 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'04',1,0)) col5,sum(decode(ajlb,'05',1,0)) col6
			,sum(decode(ajlb,'06',1,0)) col7,sum(decode(ajlb,'07',1,0)) col8,sum(decode(ajlb,'08',1,0)) col9
			,sum(decode(ajlb,'09',1,0)) col10,sum(decode(ajlb,'10',1,0)) col11,sum(decode(ajlb,'11',1,0)) col12
			,sum(decode(ajlb,'12',1,0)) col13,sum(decode(ajlb,'13',1,0)) col14,sum(decode(ajlb,'14',1,0)) col15
			,sum(decode(ajlb,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col16
			,sum(zlgzjs) col17,sum(xzcljs) col18,sum(xzcfjs) col19,sum(jgjs) col20,sum(fkjs) col21,sum(fkje) col22
			,sum(qtjs) col23,sum(qzzxjs) col24,'' col25,'' col26
	from v_a1
 where 1=1 and dwxz = '07'
/
--勞動保障公務(wù)案件統(tǒng)計(jì)表第9行分類統(tǒng)計(jì)
select 9 xh,sum(decode(aj_flag,'03',1,'04',1,0)) col1,sum(decode(ajlb,'01',1,0)) col2,sum(decode(ajlb,'02',1,0)) col3
			,sum(decode(ajlb,'03',1,0)) col4,sum(decode(ajlb,'04',1,0)) col5,sum(decode(ajlb,'05',1,0)) col6
			,sum(decode(ajlb,'06',1,0)) col7,sum(decode(ajlb,'07',1,0)) col8,sum(decode(ajlb,'08',1,0)) col9
			,sum(decode(ajlb,'09',1,0)) col10,sum(decode(ajlb,'10',1,0)) col11,sum(decode(ajlb,'11',1,0)) col12
			,sum(decode(ajlb,'12',1,0)) col13,sum(decode(ajlb,'13',1,0)) col14,sum(decode(ajlb,'14',1,0)) col15
			,sum(decode(ajlb,'15',1,'16',1,'17',1,'18',1,'19',1,'20',1,'21',1,'22',1,'23',1,'24',1,null,1,0)) col16
			,sum(zlgzjs) col17,sum(xzcljs) col18,sum(xzcfjs) col19,sum(jgjs) col20,sum(fkjs) col21,sum(fkje) col22
			,sum(qtjs) col23,sum(qzzxjs) col24,'' col25,'' col26
	from v_a1
 where 1=1 and dwxz = '08'
/