a4.sql

来自「公务员工资系统软件工程文档及源代码——JAVA编写（包括整个分析设计代码实现全过」· SQL 代码 · 共 54 行

/*******************************
--创建劳动保障公务组织统计表视图19a4
*******************************/
create or replace view v_a4_1 as
select ssbz,zjlb,sbsj sj,qx,sfjd
  from jcy_ry
/

create or replace view v_a4_2 as
select pxrs,pxqr sj,pxbm,pxbm qx
	from jcy_pxb
/
--劳动保障公务组织统计表第一行合计
select 1 xh,sum(decode(ssbz,'01',1,0)) col4,sum(decode(ssbz,'02',1,0)) col5
			,sum(1) col6,sum(decode(zjlb,'01',1,0)) col7,sum(decode(zjlb,'02',1,0)) col8
			,'' col9,'' col10
	from v_a4_1
 where 1=1
/
--劳动保障公务组织统计表第二行市级
select 2 xh,sum(decode(ssbz,'01',1,0)) col4,sum(decode(ssbz,'02',1,0)) col5
			,sum(1) col6,sum(decode(zjlb,'01',1,0)) col7,sum(decode(zjlb,'02',1,0)) col8
			,'' col9,'' col10
	from v_a4_1
 where 1=1 and qx = '001'
/
--劳动保障公务组织统计表第三行区县级
select 3 xh,sum(decode(ssbz,'01',1,0)) col4,sum(decode(ssbz,'02',1,0)) col5
			,sum(1) col6,sum(decode(zjlb,'01',1,0)) col7,sum(decode(zjlb,'02',1,0)) col8
			,'' col9,'' col10
	from v_a4_1
 where 1=1 and qx <> '001' and sfjd = '02'
/

--劳动保障公务组织统计表第四行街乡级
select 4 xh,sum(decode(ssbz,'01',1,0)) col4,sum(decode(ssbz,'02',1,0)) col5
			,sum(1) col6,sum(decode(zjlb,'01',1,0)) col7,sum(decode(zjlb,'02',1,0)) col8
			,'' col9,'' col10
	from v_a4_1
 where 1=1 and qx <> '001' and sfjd = '01'
/
--第九列的值
select 6 xh,sum(1) row1,sum(decode(pxbm,'001',1,'321',1,0)) row2
			,sum(decode(pxbm,'001',0,'321',0,1)) row3,sum(decode(pxbm,'001',0,'321',0,1)) row4
	from v_a4_2
 where 1=1
/
--第十列的值
select 7 xh,sum(pxrs) row1,sum(decode(pxbm,'001',pxrs,'321',pxrs,0)) row2
			,sum(decode(pxbm,'001',0,'321',0,pxrs)) row3,sum(decode(pxbm,'001',0,'321',0,pxrs)) row4
	from v_a4_2
 where 1=1
/