?? a4.sql
字號:
/*******************************
--創建勞動保障公務組織統計表視圖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
/
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -