/*******************************
--創建勞動保障公務組織統計表視圖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
/