b2.sql

本系统的使用可以将工作的部分流程使用计算机的办公自动化处理

/*******************************
创建劳动保障公务效果及专项工作统计表
*******************************/
create or replace view v_b2_1 as
select a.jcid,a.jctj,a.QZJBTJ,a.jcrq sj,a.ldzrs,a.qx,b.dwxz,a.dwid
	from zf_jcxx a,dw_xx b
 where a.dwid = b.dwid(+)
/
create or replace view v_b2_2 as
select a.aj_id ajid,a.dwxz dwxz,b.ldzrs ldzrs,b.jctj jctj,c.JCSM_04 qttg,c.JCSM_06 jydjk,a.dwid dwid
			,c.JCSM_05 qtwdg,c.JCSM_15 bqht,c.JCSM_23 fxdyjrs,c.JCSM_24 fxdyjje,c.jcsm_21 zfgzrs,c.jcsm_20 zfgzje
			,c.JCSM_19 jfgzrs,C.JCSM_20 JFGZJE,c.dc_flag dc_flag,c.dc_hs dc_hs,c.JCSM_02 ffjg,c.jcsm_09 zyzg,c.jcsm_10 zypx
			,c.shy_rs shy_rs,c.shy_je shy_je,c.yx_rs yx_rs,c.yx_je yx_je,c.gs_rs gs_rs,c.gs_je gs_je,c.sy_rs sy_rs,c.sy_je sy_je,c.yl_rs yl_rs,c.yl_je yl_je
			,c.MSFFSD msffsd,c.JCSM_36 wfjn,b.qzjbtj qzjbtj,a.qx qx,decode(a.blfs,'J',a.aj_djsj,'B',a.aj_lasj) sj,a.aj_flag aj_flag,a.aj_jcqk aj_jcqk
	from aj_djb a,zf_jcxx b,aj_jcxg c
 where a.jcid = b.jcid(+) and a.aj_id = c.aj_id(+)
/
-------------------------------------------
--第一列
select 1 xh,count(distinct(decode(jctj,'02',dwid,'03',dwid))) raw1,count(distinct(decode(dwxz||jctj,'0102',dwid,'0103',dwid))) raw2
			,count(distinct(decode(dwxz||jctj,'0202',dwid,'0203',dwid))) raw3,count(distinct(decode(dwxz||jctj,'0302',dwid,'0303',dwid))) raw4
			,count(distinct(decode(dwxz||jctj,'0402',dwid,'0403',dwid))) raw5,count(distinct(decode(dwxz||jctj,'0502',dwid,'0503',dwid))) raw6
			,count(distinct(decode(dwxz||jctj,'0602',dwid,'0603',dwid))) raw7,count(distinct(decode(dwxz||jctj,'0702',dwid,'0703',dwid))) raw8
			,count(distinct(decode(dwxz||jctj,'0802',dwid,'0803',dwid))) raw9
	from v_b2_1
 where 1=1
/
--第二列
select 2 xh,sum(decode(jctj,'02',ldzrs,'03',ldzrs,0)) raw1,sum(decode(dwxz||jctj,'0102',ldzrs,'0103',ldzrs,0)) raw2
			,sum(decode(dwxz||jctj,'0202',ldzrs,'0203',ldzrs,0)) raw3,sum(decode(dwxz||jctj,'0302',ldzrs,'0303',ldzrs,0)) raw4
			,sum(decode(dwxz||jctj,'0402',ldzrs,'0403',ldzrs,0)) raw5,sum(decode(dwxz||jctj,'0502',ldzrs,'0503',ldzrs,0)) raw6
			,sum(decode(dwxz||jctj,'0602',ldzrs,'0603',ldzrs,0)) raw7,sum(decode(dwxz||jctj,'0702',ldzrs,'0703',ldzrs,0)) raw8
			,sum(decode(dwxz||jctj,'0802',ldzrs,'0803',ldzrs,0)) raw9
	from v_b2_1
 where 1=1
/
--第三列
select 3 xh,sum(decode(jctj,'01',1,0)) raw1,sum(decode(dwxz||jctj,'0101',1,0)) raw2
			,sum(decode(dwxz||jctj,'0201',1,0)) raw3,sum(decode(dwxz||jctj,'0301',1,0)) raw4
			,sum(decode(dwxz||jctj,'0401',1,0)) raw5,sum(decode(dwxz||jctj,'0501',1,0)) raw6
			,sum(decode(dwxz||jctj,'0601',1,0)) raw7,sum(decode(dwxz||jctj,'0701',1,0)) raw8
			,sum(decode(dwxz||jctj,'0801',1,0)) raw9
	from v_b2_2
 where 1=1 and aj_flag in ('03','04')
/
--第四列
select 4 xh,count(distinct(decode(jctj,'04',dwid))) raw1,count(distinct(decode(dwxz||jctj,'0104',dwid))) raw2
			,count(distinct(decode(dwxz||jctj,'0204',dwid))) raw3,count(distinct(decode(dwxz||jctj,'0304',dwid))) raw4
			,count(distinct(decode(dwxz||jctj,'0404',dwid))) raw5,count(distinct(decode(dwxz||jctj,'0504',dwid))) raw6
			,count(distinct(decode(dwxz||jctj,'0604',dwid))) raw7,count(distinct(decode(dwxz||jctj,'0704',dwid))) raw8
			,count(distinct(decode(dwxz||jctj,'0804',dwid))) raw9
	from v_b2_1
 where 1=1
/
--第五列
select 5 xh,sum(decode(jctj,'04',ldzrs,0)) raw1,sum(decode(dwxz||jctj,'0104',ldzrs,0)) raw2
			,sum(decode(dwxz||jctj,'0204',ldzrs,0)) raw3,sum(decode(dwxz||jctj,'0304',ldzrs,0)) raw4
			,sum(decode(dwxz||jctj,'0404',ldzrs,0)) raw5,sum(decode(dwxz||jctj,'0504',ldzrs,0)) raw6
			,sum(decode(dwxz||jctj,'0604',ldzrs,0)) raw7,sum(decode(dwxz||jctj,'0704',ldzrs,0)) raw8
			,sum(decode(dwxz||jctj,'0804',ldzrs,0)) raw9
	from v_b2_1
 where 1=1
/
--第六列
select 6 xh,sum(decode(qzjbtj,'04',1,0)) raw1,sum(decode(dwxz||qzjbtj,'0104',1,0)) raw2
			,sum(decode(dwxz||qzjbtj,'0204',1,0)) raw3,sum(decode(dwxz||qzjbtj,'0304',1,0)) raw4
			,sum(decode(dwxz||qzjbtj,'0404',1,0)) raw5,sum(decode(dwxz||qzjbtj,'0504',1,0)) raw6
			,sum(decode(dwxz||qzjbtj,'0604',1,0)) raw7,sum(decode(dwxz||qzjbtj,'0704',1,0)) raw8
			,sum(decode(dwxz||qzjbtj,'0804',1,0)) raw9
	from v_b2_2
 where 1=1
/
--第七列
select 7 xh,sum(decode(qzjbtj,'04',ldzrs,0)) raw1,sum(decode(dwxz||qzjbtj,'0104',ldzrs,0)) raw2
			,sum(decode(dwxz||qzjbtj,'0204',ldzrs,0)) raw3,sum(decode(dwxz||qzjbtj,'0304',ldzrs,0)) raw4
			,sum(decode(dwxz||qzjbtj,'0404',ldzrs,0)) raw5,sum(decode(dwxz||qzjbtj,'0504',ldzrs,0)) raw6
			,sum(decode(dwxz||qzjbtj,'0604',ldzrs,0)) raw7,sum(decode(dwxz||qzjbtj,'0704',ldzrs,0)) raw8
			,sum(decode(dwxz||qzjbtj,'0804',ldzrs,0)) raw9
	from v_b2_2
 where 1=1
/
--第八列
select 8 xh,sum(bqht) raw1,sum(decode(dwxz,'01',bqht,0)) raw2
			,sum(decode(dwxz,'02',bqht,0)) raw3,sum(decode(dwxz,'03',bqht,0)) raw4
			,sum(decode(dwxz,'04',bqht,0)) raw5,sum(decode(dwxz,'05',bqht,0)) raw6
			,sum(decode(dwxz,'06',bqht,0)) raw7,sum(decode(dwxz,'07',bqht,0)) raw8
			,sum(decode(dwxz,'08',bqht,0)) raw9
	from v_b2_2
 where 1=1
/
--第九列
select 9 xh,sum(zfgzrs) raw1,sum(decode(dwxz,'01',zfgzrs,0)) raw2
			,sum(decode(dwxz,'02',zfgzrs,0)) raw3,sum(decode(dwxz,'03',zfgzrs,0)) raw4
			,sum(decode(dwxz,'04',zfgzrs,0)) raw5,sum(decode(dwxz,'05',zfgzrs,0)) raw6
			,sum(decode(dwxz,'06',zfgzrs,0)) raw7,sum(decode(dwxz,'07',zfgzrs,0)) raw8
			,sum(decode(dwxz,'08',zfgzrs,0)) raw9
	from v_b2_2
 where 1=1
/
--第十列
select 10 xh,sum(zfgzje) raw1,sum(decode(dwxz,'01',zfgzje,0)) raw2
			,sum(decode(dwxz,'02',zfgzje,0)) raw3,sum(decode(dwxz,'03',zfgzje,0)) raw4
			,sum(decode(dwxz,'04',zfgzje,0)) raw5,sum(decode(dwxz,'05',zfgzje,0)) raw6
			,sum(decode(dwxz,'06',zfgzje,0)) raw7,sum(decode(dwxz,'07',zfgzje,0)) raw8
			,sum(decode(dwxz,'08',zfgzje,0)) raw9
	from v_b2_2
 where 1=1
/
--第十一列
select 11 xh,sum(decode(dc_flag,'01',dc_hs,0)) raw1,sum(decode(dwxz,'01',decode(dc_flag,'01',dc_hs,0),0)) raw2
			,sum(decode(dwxz,'02',decode(dc_flag,'01',dc_hs,0),0)) raw3,sum(decode(dwxz,'03',decode(dc_flag,'01',dc_hs,0),0)) raw4
			,sum(decode(dwxz,'04',decode(dc_flag,'01',dc_hs,0),0)) raw5,sum(decode(dwxz,'05',decode(dc_flag,'01',dc_hs,0),0)) raw6
			,sum(decode(dwxz,'06',decode(dc_flag,'01',dc_hs,0),0)) raw7,sum(decode(dwxz,'07',decode(dc_flag,'01',dc_hs,0),0)) raw8
			,sum(decode(dwxz,'08',decode(dc_flag,'01',dc_hs,0),0)) raw9
	from v_b2_2
 where 1=1
/
--第十二列
select 12 xh,sum(decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) raw1
			,sum(decode(dwxz,'01',decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0),0)) raw2
			,sum(decode(dwxz,'02',decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0),0)) raw3
			,sum(decode(dwxz,'03',decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0),0)) raw4
			,sum(decode(dwxz,'04',decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0),0)) raw5
			,sum(decode(dwxz,'05',decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0),0)) raw6
			,sum(decode(dwxz,'06',decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0),0)) raw7
			,sum(decode(dwxz,'07',decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0),0)) raw8
			,sum(decode(dwxz,'08',decode(dc_flag,'01',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0),0)) raw9
	from v_b2_2
 where 1=1
/
--第十三列
select 13 xh,sum(decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0)) raw1
			,sum(decode(dwxz,'01',decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0),0)) raw2
			,sum(decode(dwxz,'02',decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0),0)) raw3
			,sum(decode(dwxz,'03',decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0),0)) raw4
			,sum(decode(dwxz,'04',decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0),0)) raw5
			,sum(decode(dwxz,'05',decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0),0)) raw6
			,sum(decode(dwxz,'06',decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0),0)) raw7
			,sum(decode(dwxz,'07',decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0),0)) raw8
			,sum(decode(dwxz,'08',decode(dc_flag,'01',nvl(shy_je,0)+nvl(yx_je,0)+nvl(gs_je,0)+nvl(sy_je,0)+nvl(yl_je,0),0),0)) raw9
	from v_b2_2
 where 1=1
/
--第十四列
select 14 xh,sum(decode(dc_flag,'02',dc_hs,0)) raw1,sum(decode(dwxz,'01',decode(dc_flag,'02',dc_hs,0),0)) raw2
			,sum(decode(dwxz,'02',decode(dc_flag,'02',dc_hs,0),0)) raw3,sum(decode(dwxz,'03',decode(dc_flag,'02',dc_hs,0),0)) raw4
			,sum(decode(dwxz,'04',decode(dc_flag,'02',dc_hs,0),0)) raw5,sum(decode(dwxz,'05',decode(dc_flag,'02',dc_hs,0),0)) raw6
			,sum(decode(dwxz,'06',decode(dc_flag,'02',dc_hs,0),0)) raw7,sum(decode(dwxz,'07',decode(dc_flag,'02',dc_hs,0),0)) raw8
			,sum(decode(dwxz,'08',decode(dc_flag,'02',dc_hs,0),0)) raw9
	from v_b2_2
 where 1=1
/
--第十五列
select 15 xh,sum(decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0)) raw1
			,sum(decode(dwxz,'01',decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0),0)) raw2
			,sum(decode(dwxz,'02',decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0),0)) raw3
			,sum(decode(dwxz,'03',decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0),0)) raw4
			,sum(decode(dwxz,'04',decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0),0)) raw5
			,sum(decode(dwxz,'05',decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0),0)) raw6
			,sum(decode(dwxz,'06',decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0),0)) raw7
			,sum(decode(dwxz,'07',decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0),0)) raw8
			,sum(decode(dwxz,'08',decode(dc_flag,'02',nvl(shy_rs,0)+nvl(yx_rs,0)+nvl(gs_rs,0)+nvl(sy_rs,0)+nvl(yl_rs,0),0),0)) raw9
	from v_b2_2
 where 1=1
/

--第十行
select 10 xh,sum(ffjg) col1,sum(qttg) col2,sum(jydjk) col3,sum(qtwdg) col4,count(decode(zypx||zyzg,null,null,'00',null,ajid)) col5,sum(fxdyjrs) col6
			,sum(fxdyjje) col7,'' col8,sum(decode(jctj,'01',1,0)) col9,sum(decode(jctj,'01',decode(aj_flag,'02',1,0),0)) col10
			,'' col11,'' col12,'' col13,'' col14
	from v_b2_2
 where 1=1
/
--第十行第8列
select sum(decode(jctj,'01',1,0)) col8
	from v_b2_1
 where 1=1
/
--第十行第12列
select count(distinct(dwid))  col12
  from v_a2_2
 where 1=1 and sfhg = '02'
/
--第十行第13列
select count(distinct(dwid))  col13
  from v_a2_1
 where 1=1
/
--第十行第14列
select sum(jzcs)  col14
  from v_a2_1
 where 1=1
/