c1.sql

公务员管理系统

/*******************************
--创建对用人单位专项检查情况统计表视图
*******************************/
create or replace view v_c1 as
select a.jcid jcid,a.dwid dwid,dp_sys.f_get_dwxz_by_dwid(a.dwid) dwxz,a.jcrq sj,a.ldzrs ldzrs,a.qx qx
			,c.aj_flag aj_flag,c.aj_id ajid,c.fkje fkje
			,(select sum(decode(e.mbid,'ZLGZ_HT',1,'ZLGZ_SX',1,'ZLGZ_GZ',1,'ZLGZ_QT',1,0)) from aj_ml e where c.aj_id = e.aj_id) zlgzjs
			,(select sum(decode(e.mbid,'XZCL_HT',1,'XZCL_SX',1,'XZCL_GZ',1,'XZCL_QT',1,0)) from aj_ml e where c.aj_id = e.aj_id) xzcljs
			,(select sum(decode(e.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 e where c.aj_id = e.aj_id) xzcfjs
			,(select sum(decode(e.mbid,'QZZX_HT',1,'QZZX_SX',1,'QZZX_GZ',1,'QZZX_YG',1,'QZZX_QT',1,0)) from aj_ml e where c.aj_id = e.aj_id) qzzxjs
			,d.shbxzjxs ctfs,d.yl_je ylje,d.sy_je syje,d.yx_je yxje,d.gs_je gsje
			,d.yl_rs ylrs,d.sy_rs syrs,d.yx_rs yxrs,d.gs_rs gsrs,a.zxjc zxjc
	from zf_jcxx a,aj_djb c,aj_jcxg d
 where a.jcid = c.jcid(+)  and c.aj_id = d.aj_id(+) and a.jctj = '03'
/
--用人单位专项检查情况统计表第一行合计
select 1 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(decode(ajid,null,0,1)) col3
			,sum(decode(aj_flag,'03',1,'04',1,0)) col4,sum(zlgzjs) col5,sum(xzcljs) col6
			,sum(xzcfjs) col7,sum(fkje) col8,count(distinct(decode(ctfs,'01',dwid))) col9
			,sum(decode(ctfs,'01',ldzrs,0)) col10,sum(decode(ctfs,'01',ylje,0)) col11,sum(decode(ctfs,'01',syje,0)) col12
			,sum(decode(ctfs,'01',yxje,0)) col13,sum(decode(ctfs,'01',gsje,0)) col14,count(distinct(decode(ctfs,'02',dwid))) col15
			,sum(decode(ctfs,'02',ldzrs,0)) col16,sum(decode(ctfs,'02',ylje,0)) col17,sum(decode(ctfs,'02',syje,0)) col18
			,sum(decode(ctfs,'02',yxje,0)) col19,sum(decode(ctfs,'02',gsje,0)) col20,count(distinct(decode(ctfs,'03',dwid))) col21
			,sum(decode(ctfs,'03',ldzrs,0)) col22,sum(decode(ctfs,'03',ylje,0)) col23,sum(decode(ctfs,'03',syje,0)) col24
			,sum(decode(ctfs,'03',yxje,0)) col25,sum(decode(ctfs,'03',gsje,0)) col26,sum(qzzxjs) col27
	from v_c1
 where 1=1
/
--用人单位专项检查情况统计表第2行
select 2 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(decode(ajid,null,0,1)) col3
			,sum(decode(aj_flag,'03',1,'04',1,0)) col4,sum(zlgzjs) col5,sum(xzcljs) col6
			,sum(xzcfjs) col7,sum(fkje) col8,count(distinct(decode(ctfs,'01',dwid))) col9
			,sum(decode(ctfs,'01',ldzrs,0)) col10,sum(decode(ctfs,'01',ylje,0)) col11,sum(decode(ctfs,'01',syje,0)) col12
			,sum(decode(ctfs,'01',yxje,0)) col13,sum(decode(ctfs,'01',gsje,0)) col14,count(distinct(decode(ctfs,'02',dwid))) col15
			,sum(decode(ctfs,'02',ldzrs,0)) col16,sum(decode(ctfs,'02',ylje,0)) col17,sum(decode(ctfs,'02',syje,0)) col18
			,sum(decode(ctfs,'02',yxje,0)) col19,sum(decode(ctfs,'02',gsje,0)) col20,count(distinct(decode(ctfs,'03',dwid))) col21
			,sum(decode(ctfs,'03',ldzrs,0)) col22,sum(decode(ctfs,'03',ylje,0)) col23,sum(decode(ctfs,'03',syje,0)) col24
			,sum(decode(ctfs,'03',yxje,0)) col25,sum(decode(ctfs,'03',gsje,0)) col26,sum(qzzxjs) col27
	from v_c1
 where 1=1 and dwxz = '01'
/
--用人单位专项检查情况统计表第3行
select 3 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(decode(ajid,null,0,1)) col3
			,sum(decode(aj_flag,'03',1,'04',1,0)) col4,sum(zlgzjs) col5,sum(xzcljs) col6
			,sum(xzcfjs) col7,sum(fkje) col8,count(distinct(decode(ctfs,'01',dwid))) col9
			,sum(decode(ctfs,'01',ldzrs,0)) col10,sum(decode(ctfs,'01',ylje,0)) col11,sum(decode(ctfs,'01',syje,0)) col12
			,sum(decode(ctfs,'01',yxje,0)) col13,sum(decode(ctfs,'01',gsje,0)) col14,count(distinct(decode(ctfs,'02',dwid))) col15
			,sum(decode(ctfs,'02',ldzrs,0)) col16,sum(decode(ctfs,'02',ylje,0)) col17,sum(decode(ctfs,'02',syje,0)) col18
			,sum(decode(ctfs,'02',yxje,0)) col19,sum(decode(ctfs,'02',gsje,0)) col20,count(distinct(decode(ctfs,'03',dwid))) col21
			,sum(decode(ctfs,'03',ldzrs,0)) col22,sum(decode(ctfs,'03',ylje,0)) col23,sum(decode(ctfs,'03',syje,0)) col24
			,sum(decode(ctfs,'03',yxje,0)) col25,sum(decode(ctfs,'03',gsje,0)) col26,sum(qzzxjs) col27
	from v_c1
 where 1=1 and dwxz = '02'
/
--用人单位专项检查情况统计表第4行
select 4 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(decode(ajid,null,0,1)) col3
			,sum(decode(aj_flag,'03',1,'04',1,0)) col4,sum(zlgzjs) col5,sum(xzcljs) col6
			,sum(xzcfjs) col7,sum(fkje) col8,count(distinct(decode(ctfs,'01',dwid))) col9
			,sum(decode(ctfs,'01',ldzrs,0)) col10,sum(decode(ctfs,'01',ylje,0)) col11,sum(decode(ctfs,'01',syje,0)) col12
			,sum(decode(ctfs,'01',yxje,0)) col13,sum(decode(ctfs,'01',gsje,0)) col14,count(distinct(decode(ctfs,'02',dwid))) col15
			,sum(decode(ctfs,'02',ldzrs,0)) col16,sum(decode(ctfs,'02',ylje,0)) col17,sum(decode(ctfs,'02',syje,0)) col18
			,sum(decode(ctfs,'02',yxje,0)) col19,sum(decode(ctfs,'02',gsje,0)) col20,count(distinct(decode(ctfs,'03',dwid))) col21
			,sum(decode(ctfs,'03',ldzrs,0)) col22,sum(decode(ctfs,'03',ylje,0)) col23,sum(decode(ctfs,'03',syje,0)) col24
			,sum(decode(ctfs,'03',yxje,0)) col25,sum(decode(ctfs,'03',gsje,0)) col26,sum(qzzxjs) col27
	from v_c1
 where 1=1 and dwxz = '03'
/
--用人单位专项检查情况统计表第5行
select 5 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(decode(ajid,null,0,1)) col3
			,sum(decode(aj_flag,'03',1,'04',1,0)) col4,sum(zlgzjs) col5,sum(xzcljs) col6
			,sum(xzcfjs) col7,sum(fkje) col8,count(distinct(decode(ctfs,'01',dwid))) col9
			,sum(decode(ctfs,'01',ldzrs,0)) col10,sum(decode(ctfs,'01',ylje,0)) col11,sum(decode(ctfs,'01',syje,0)) col12
			,sum(decode(ctfs,'01',yxje,0)) col13,sum(decode(ctfs,'01',gsje,0)) col14,count(distinct(decode(ctfs,'02',dwid))) col15
			,sum(decode(ctfs,'02',ldzrs,0)) col16,sum(decode(ctfs,'02',ylje,0)) col17,sum(decode(ctfs,'02',syje,0)) col18
			,sum(decode(ctfs,'02',yxje,0)) col19,sum(decode(ctfs,'02',gsje,0)) col20,count(distinct(decode(ctfs,'03',dwid))) col21
			,sum(decode(ctfs,'03',ldzrs,0)) col22,sum(decode(ctfs,'03',ylje,0)) col23,sum(decode(ctfs,'03',syje,0)) col24
			,sum(decode(ctfs,'03',yxje,0)) col25,sum(decode(ctfs,'03',gsje,0)) col26,sum(qzzxjs) col27
	from v_c1
 where 1=1 and dwxz = '04'
/
--用人单位专项检查情况统计表第6行
select 6 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(decode(ajid,null,0,1)) col3
			,sum(decode(aj_flag,'03',1,'04',1,0)) col4,sum(zlgzjs) col5,sum(xzcljs) col6
			,sum(xzcfjs) col7,sum(fkje) col8,count(distinct(decode(ctfs,'01',dwid))) col9
			,sum(decode(ctfs,'01',ldzrs,0)) col10,sum(decode(ctfs,'01',ylje,0)) col11,sum(decode(ctfs,'01',syje,0)) col12
			,sum(decode(ctfs,'01',yxje,0)) col13,sum(decode(ctfs,'01',gsje,0)) col14,count(distinct(decode(ctfs,'02',dwid))) col15
			,sum(decode(ctfs,'02',ldzrs,0)) col16,sum(decode(ctfs,'02',ylje,0)) col17,sum(decode(ctfs,'02',syje,0)) col18
			,sum(decode(ctfs,'02',yxje,0)) col19,sum(decode(ctfs,'02',gsje,0)) col20,count(distinct(decode(ctfs,'03',dwid))) col21
			,sum(decode(ctfs,'03',ldzrs,0)) col22,sum(decode(ctfs,'03',ylje,0)) col23,sum(decode(ctfs,'03',syje,0)) col24
			,sum(decode(ctfs,'03',yxje,0)) col25,sum(decode(ctfs,'03',gsje,0)) col26,sum(qzzxjs) col27
	from v_c1
 where 1=1 and dwxz = '05'
/
--用人单位专项检查情况统计表第7行
select 7 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(decode(ajid,null,0,1)) col3
			,sum(decode(aj_flag,'03',1,'04',1,0)) col4,sum(zlgzjs) col5,sum(xzcljs) col6
			,sum(xzcfjs) col7,sum(fkje) col8,count(distinct(decode(ctfs,'01',dwid))) col9
			,sum(decode(ctfs,'01',ldzrs,0)) col10,sum(decode(ctfs,'01',ylje,0)) col11,sum(decode(ctfs,'01',syje,0)) col12
			,sum(decode(ctfs,'01',yxje,0)) col13,sum(decode(ctfs,'01',gsje,0)) col14,count(distinct(decode(ctfs,'02',dwid))) col15
			,sum(decode(ctfs,'02',ldzrs,0)) col16,sum(decode(ctfs,'02',ylje,0)) col17,sum(decode(ctfs,'02',syje,0)) col18
			,sum(decode(ctfs,'02',yxje,0)) col19,sum(decode(ctfs,'02',gsje,0)) col20,count(distinct(decode(ctfs,'03',dwid))) col21
			,sum(decode(ctfs,'03',ldzrs,0)) col22,sum(decode(ctfs,'03',ylje,0)) col23,sum(decode(ctfs,'03',syje,0)) col24
			,sum(decode(ctfs,'03',yxje,0)) col25,sum(decode(ctfs,'03',gsje,0)) col26,sum(qzzxjs) col27
	from v_c1
 where 1=1 and dwxz = '06'
/
--用人单位专项检查情况统计表第8行
select 8 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(decode(ajid,null,0,1)) col3
			,sum(decode(aj_flag,'03',1,'04',1,0)) col4,sum(zlgzjs) col5,sum(xzcljs) col6
			,sum(xzcfjs) col7,sum(fkje) col8,count(distinct(decode(ctfs,'01',dwid))) col9
			,sum(decode(ctfs,'01',ldzrs,0)) col10,sum(decode(ctfs,'01',ylje,0)) col11,sum(decode(ctfs,'01',syje,0)) col12
			,sum(decode(ctfs,'01',yxje,0)) col13,sum(decode(ctfs,'01',gsje,0)) col14,count(distinct(decode(ctfs,'02',dwid))) col15
			,sum(decode(ctfs,'02',ldzrs,0)) col16,sum(decode(ctfs,'02',ylje,0)) col17,sum(decode(ctfs,'02',syje,0)) col18
			,sum(decode(ctfs,'02',yxje,0)) col19,sum(decode(ctfs,'02',gsje,0)) col20,count(distinct(decode(ctfs,'03',dwid))) col21
			,sum(decode(ctfs,'03',ldzrs,0)) col22,sum(decode(ctfs,'03',ylje,0)) col23,sum(decode(ctfs,'03',syje,0)) col24
			,sum(decode(ctfs,'03',yxje,0)) col25,sum(decode(ctfs,'03',gsje,0)) col26,sum(qzzxjs) col27
	from v_c1
 where 1=1 and dwxz = '07'
/
--用人单位专项检查情况统计表第9行
select 9 xh,count(distinct(dwid)) col1,sum(ldzrs) col2,sum(decode(ajid,null,0,1)) col3
			,sum(decode(aj_flag,'03',1,'04',1,0)) col4,sum(zlgzjs) col5,sum(xzcljs) col6
			,sum(xzcfjs) col7,sum(fkje) col8,count(distinct(decode(ctfs,'01',dwid))) col9
			,sum(decode(ctfs,'01',ldzrs,0)) col10,sum(decode(ctfs,'01',ylje,0)) col11,sum(decode(ctfs,'01',syje,0)) col12
			,sum(decode(ctfs,'01',yxje,0)) col13,sum(decode(ctfs,'01',gsje,0)) col14,count(distinct(decode(ctfs,'02',dwid))) col15
			,sum(decode(ctfs,'02',ldzrs,0)) col16,sum(decode(ctfs,'02',ylje,0)) col17,sum(decode(ctfs,'02',syje,0)) col18
			,sum(decode(ctfs,'02',yxje,0)) col19,sum(decode(ctfs,'02',gsje,0)) col20,count(distinct(decode(ctfs,'03',dwid))) col21
			,sum(decode(ctfs,'03',ldzrs,0)) col22,sum(decode(ctfs,'03',ylje,0)) col23,sum(decode(ctfs,'03',syje,0)) col24
			,sum(decode(ctfs,'03',yxje,0)) col25,sum(decode(ctfs,'03',gsje,0)) col26,sum(qzzxjs) col27
	from v_c1
 where 1=1 and dwxz = '08'
/