select * from
(select f.TYPE_MEI_ZKK as name , col1,col2,col3,col4,col5,round(col2/case when col4=0 then 1 else col4 end,2) as col6
, col7,col8,col9,col10,col11,round(col8/case when col10=0 then 1 else col10 end,2) as col12
, col13,col14,col15,col16,col17,round(col14/case when col16=0 then 1 else col16 end,2) as col18
, col19,col20,col21,col22,col23,round(col20/case when col22=0 then 1 else col22 end,2) as col24
, col25,col26,col27,col28,col29,round(col26/case when col28=0 then 1 else col28 end,2) as col30
, col31,col32,col33,col34,col35,round(col32/case when col34=0 then 1 else col34 end,2) as col36
, col37,col38,col39,col40,col41,round(col38/case when col40=0 then 1 else col40 end,2) as col42
,f.type_cd as code,rowcol
from
(select sum(case when e.querydate = e.dateorder then nvl(e.KEIKAKU_SU,0) else 0 end) as col1 ,
sum(case when e.querydate = e.dateorder then nvl(e.JISEKI_SU,0) else 0 end) as col2 ,
sum(case when e.querydate = e.dateorder then nvl(e.KABUSOKU_SU,0) else 0 end) as col3 ,
sum(case when e.querydate = e.dateorder then nvl(e.ZAN_SU,0) else 0 end) as col4 ,
sum(case when e.querydate = e.dateorder then nvl(e.FUDOU_SU,0) else 0 end) as col5 ,
sum(case when e.querydate - 1 = e.dateorder then nvl(e.KEIKAKU_SU,0) else 0 end) as col7 ,
sum(case when e.querydate - 1 = e.dateorder then nvl(e.JISEKI_SU,0) else 0 end) as col8 ,
sum(case when e.querydate - 1 = e.dateorder then nvl(e.KABUSOKU_SU,0) else 0 end) as col9 ,
sum(case when e.querydate - 1 = e.dateorder then nvl(e.ZAN_SU,0) else 0 end) as col10 ,
sum(case when e.querydate - 1 = e.dateorder then nvl(e.FUDOU_SU,0) else 0 end) as col11 ,
sum(case when e.querydate - 2 = e.dateorder then nvl(e.KEIKAKU_SU,0) else 0 end) as col13 ,
sum(case when e.querydate - 2 = e.dateorder then nvl(e.JISEKI_SU,0) else 0 end) as col14,
sum(case when e.querydate - 2 = e.dateorder then nvl(e.KABUSOKU_SU,0) else 0 end) as col15 ,
sum(case when e.querydate - 2 = e.dateorder then nvl(e.ZAN_SU,0) else 0 end) as col16 ,
sum(case when e.querydate - 2 = e.dateorder then nvl(e.FUDOU_SU,0) else 0 end) as col17 ,
sum(case when e.querydate - 3 = e.dateorder then nvl(e.KEIKAKU_SU,0) else 0 end) as col19 ,
sum(case when e.querydate - 3 = e.dateorder then nvl(e.JISEKI_SU,0) else 0 end) as col20,
sum(case when e.querydate - 3 = e.dateorder then nvl(e.KABUSOKU_SU,0) else 0 end) as col21 ,
sum(case when e.querydate - 3 = e.dateorder then nvl(e.ZAN_SU,0) else 0 end) as col22 ,
sum(case when e.querydate - 3 = e.dateorder then nvl(e.FUDOU_SU,0) else 0 end) as col23 ,
sum(case when e.querydate - 4 = e.dateorder then nvl(e.KEIKAKU_SU,0) else 0 end) as col25 ,
sum(case when e.querydate - 4 = e.dateorder then nvl(e.JISEKI_SU,0) else 0 end) as col26,
sum(case when e.querydate - 4 = e.dateorder then nvl(e.KABUSOKU_SU,0) else 0 end) as col27 ,
sum(case when e.querydate - 4 = e.dateorder then nvl(e.ZAN_SU,0) else 0 end) as col28 ,
sum(case when e.querydate - 4 = e.dateorder then nvl(e.FUDOU_SU,0) else 0 end) as col29,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.KEIKAKU_SU,0) else 0 end) as col31 ,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.JISEKI_SU,0) else 0 end) as col32,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.KABUSOKU_SU,0) else 0 end) as col33 ,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.ZAN_SU,0) else 0 end) as col34 ,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.FUDOU_SU,0) else 0 end) as col35,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.KEIKAKU_SU,0) else 0 end) as col37 ,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.JISEKI_SU,0) else 0 end) as col38,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.KABUSOKU_SU,0) else 0 end) as col39 ,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.ZAN_SU,0) else 0 end) as col40 ,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.FUDOU_SU,0) else 0 end) as col41,e.type_cd,
min(e.TYPE_MEI_ZKK) as TYPE_MEI_ZKK,min(rownum) as rowcol
from
(select to_char(sysdate,'yyyymmdd') as querydate,c.dateorder,d.*
from
(select b.type_cd as type_cd, dateorder as dateorder
from
(select to_char(sysdate - 1,'yyyymmdd') as dateorder from dual
union all select to_char(sysdate - 2,'yyyymmdd') as dateorder from dual
union all select to_char(sysdate - 3,'yyyymmdd') as dateorder from dual
union all select to_char(sysdate - 4,'yyyymmdd') as dateorder from dual
union all select to_char(sysdate - 5,'yyyymmdd') as dateorder from dual
union all select to_char(sysdate - 6,'yyyymmdd') as dateorder from dual
union all select to_char(sysdate ,'yyyymmdd') as dateorder from dual ) a ,
(select TYPE_CD from TAPFH_WTYPE_BETU_WORK) b ) c ,
(select * from TAPFH_WTYPE_BETU_WORK) d
where c.dateorder=d.TAISYO_HZK(+) and c.type_cd=d.type_cd(+)
) e group by e.type_cd
) f ) g ,(select 1 as rowcol from dual union all select 2 as rowcol from dual
union all select 3 as rowcol from dual
union all select 4 as rowcol from dual
union all select 5 as rowcol from dual
union all select 6 as rowcol from dual
union all select 7 as rowcol from dual
union all select 8 as rowcol from dual
union all select 9 as rowcol from dual
union all select 10 as rowcol from dual
union all select 11 as rowcol from dual
union all select 12 as rowcol from dual
union all select 13 as rowcol from dual ) h where h.rowcol=g.rowcol(+)
(select f.TYPE_MEI_ZKK as name , col1,col2,col3,col4,col5,round(col2/case when col4=0 then 1 else col4 end,2) as col6
, col7,col8,col9,col10,col11,round(col8/case when col10=0 then 1 else col10 end,2) as col12
, col13,col14,col15,col16,col17,round(col14/case when col16=0 then 1 else col16 end,2) as col18
, col19,col20,col21,col22,col23,round(col20/case when col22=0 then 1 else col22 end,2) as col24
, col25,col26,col27,col28,col29,round(col26/case when col28=0 then 1 else col28 end,2) as col30
, col31,col32,col33,col34,col35,round(col32/case when col34=0 then 1 else col34 end,2) as col36
, col37,col38,col39,col40,col41,round(col38/case when col40=0 then 1 else col40 end,2) as col42
,f.type_cd as code,rowcol
from
(select sum(case when e.querydate = e.dateorder then nvl(e.KEIKAKU_SU,0) else 0 end) as col1 ,
sum(case when e.querydate = e.dateorder then nvl(e.JISEKI_SU,0) else 0 end) as col2 ,
sum(case when e.querydate = e.dateorder then nvl(e.KABUSOKU_SU,0) else 0 end) as col3 ,
sum(case when e.querydate = e.dateorder then nvl(e.ZAN_SU,0) else 0 end) as col4 ,
sum(case when e.querydate = e.dateorder then nvl(e.FUDOU_SU,0) else 0 end) as col5 ,
sum(case when e.querydate - 1 = e.dateorder then nvl(e.KEIKAKU_SU,0) else 0 end) as col7 ,
sum(case when e.querydate - 1 = e.dateorder then nvl(e.JISEKI_SU,0) else 0 end) as col8 ,
sum(case when e.querydate - 1 = e.dateorder then nvl(e.KABUSOKU_SU,0) else 0 end) as col9 ,
sum(case when e.querydate - 1 = e.dateorder then nvl(e.ZAN_SU,0) else 0 end) as col10 ,
sum(case when e.querydate - 1 = e.dateorder then nvl(e.FUDOU_SU,0) else 0 end) as col11 ,
sum(case when e.querydate - 2 = e.dateorder then nvl(e.KEIKAKU_SU,0) else 0 end) as col13 ,
sum(case when e.querydate - 2 = e.dateorder then nvl(e.JISEKI_SU,0) else 0 end) as col14,
sum(case when e.querydate - 2 = e.dateorder then nvl(e.KABUSOKU_SU,0) else 0 end) as col15 ,
sum(case when e.querydate - 2 = e.dateorder then nvl(e.ZAN_SU,0) else 0 end) as col16 ,
sum(case when e.querydate - 2 = e.dateorder then nvl(e.FUDOU_SU,0) else 0 end) as col17 ,
sum(case when e.querydate - 3 = e.dateorder then nvl(e.KEIKAKU_SU,0) else 0 end) as col19 ,
sum(case when e.querydate - 3 = e.dateorder then nvl(e.JISEKI_SU,0) else 0 end) as col20,
sum(case when e.querydate - 3 = e.dateorder then nvl(e.KABUSOKU_SU,0) else 0 end) as col21 ,
sum(case when e.querydate - 3 = e.dateorder then nvl(e.ZAN_SU,0) else 0 end) as col22 ,
sum(case when e.querydate - 3 = e.dateorder then nvl(e.FUDOU_SU,0) else 0 end) as col23 ,
sum(case when e.querydate - 4 = e.dateorder then nvl(e.KEIKAKU_SU,0) else 0 end) as col25 ,
sum(case when e.querydate - 4 = e.dateorder then nvl(e.JISEKI_SU,0) else 0 end) as col26,
sum(case when e.querydate - 4 = e.dateorder then nvl(e.KABUSOKU_SU,0) else 0 end) as col27 ,
sum(case when e.querydate - 4 = e.dateorder then nvl(e.ZAN_SU,0) else 0 end) as col28 ,
sum(case when e.querydate - 4 = e.dateorder then nvl(e.FUDOU_SU,0) else 0 end) as col29,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.KEIKAKU_SU,0) else 0 end) as col31 ,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.JISEKI_SU,0) else 0 end) as col32,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.KABUSOKU_SU,0) else 0 end) as col33 ,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.ZAN_SU,0) else 0 end) as col34 ,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.FUDOU_SU,0) else 0 end) as col35,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.KEIKAKU_SU,0) else 0 end) as col37 ,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.JISEKI_SU,0) else 0 end) as col38,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.KABUSOKU_SU,0) else 0 end) as col39 ,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.ZAN_SU,0) else 0 end) as col40 ,
sum(case when e.querydate - 5 = e.dateorder then nvl(e.FUDOU_SU,0) else 0 end) as col41,e.type_cd,
min(e.TYPE_MEI_ZKK) as TYPE_MEI_ZKK,min(rownum) as rowcol
from
(select to_char(sysdate,'yyyymmdd') as querydate,c.dateorder,d.*
from
(select b.type_cd as type_cd, dateorder as dateorder
from
(select to_char(sysdate - 1,'yyyymmdd') as dateorder from dual
union all select to_char(sysdate - 2,'yyyymmdd') as dateorder from dual
union all select to_char(sysdate - 3,'yyyymmdd') as dateorder from dual
union all select to_char(sysdate - 4,'yyyymmdd') as dateorder from dual
union all select to_char(sysdate - 5,'yyyymmdd') as dateorder from dual
union all select to_char(sysdate - 6,'yyyymmdd') as dateorder from dual
union all select to_char(sysdate ,'yyyymmdd') as dateorder from dual ) a ,
(select TYPE_CD from TAPFH_WTYPE_BETU_WORK) b ) c ,
(select * from TAPFH_WTYPE_BETU_WORK) d
where c.dateorder=d.TAISYO_HZK(+) and c.type_cd=d.type_cd(+)
) e group by e.type_cd
) f ) g ,(select 1 as rowcol from dual union all select 2 as rowcol from dual
union all select 3 as rowcol from dual
union all select 4 as rowcol from dual
union all select 5 as rowcol from dual
union all select 6 as rowcol from dual
union all select 7 as rowcol from dual
union all select 8 as rowcol from dual
union all select 9 as rowcol from dual
union all select 10 as rowcol from dual
union all select 11 as rowcol from dual
union all select 12 as rowcol from dual
union all select 13 as rowcol from dual ) h where h.rowcol=g.rowcol(+)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货