要优化的SQL

with view1 as(
select ym,cgicode,ncicvtpric from(
select to_date(stockyear || '-' || stockmonth,'yyyy-mm') ym,st.cgicode,sum(nvl(st.vtexceupric,0)*nvl(qty,0)) ncicvtpric
from stocklist st where st.cgicode in (select cgicode from t_tmp) and dc = 1
group by stockyear,stockmonth,st.cgicode
order by st.cgicode,to_date(stockyear || '-' || stockmonth,'yyyy-mm'))
)
select t1.cgicode,
sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200712')) as "200712",
sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200801')) as "200801",
sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200802')) as "200802",
 sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200803')) as "200803",
 sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200804')) as "200804",
 sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200805')) as "200805",
 sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200806')) as "200806",
 sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200807')) as "200807",
 sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200808')) as "200808",
 sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200809')) as "200809",
 sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200810')) as "200810",
 sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200811')) as "200811",
 sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200812')) as "200812",
 sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200901')) as "200901",
 sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200902')) as "200902",
 sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200903')) as "200903",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200904')) as "200904",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200905')) as "200905",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200906')) as "200906",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200907')) as "200907",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200908')) as "200908",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200909')) as "200909",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200910')) as "200910",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200911')) as "200911",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='200912')) as "200912",
   sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='201001')) as "201001",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='201002')) as "201002",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='201003')) as "201003",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='201004')) as "201004",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='201005')) as "201005",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='201006')) as "201006",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='201007')) as "201007",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='201008')) as "201008",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='201009')) as "201009",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='201010')) as "201010",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='201011')) as "201011",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='201012')) as "201012",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='201101')) as "201101",
  sum((select t2.ncicvtpric
 from view1 t2
 where t2.cgicode=t1.cgicode
 and t2.ym=t1.ym
 and to_char(t2.ym,'yyyymm')='201102')) as "201102"
from view1 t1
group by t1.cgicode

 

 

转化后：

with view1 as(
select ym,cgicode,ncicvtpric from(
select to_date(stockyear || '-' || stockmonth,'yyyy-mm') ym,st.cgicode,sum(nvl(st.vtexceupric,0)*nvl(qty,0)) ncicvtpric
from stocklist st where st.cgicode in (select cgicode from t_tmp) and dc = 1
group by stockyear,stockmonth,st.cgicode
order by st.cgicode,to_date(stockyear || '-' || stockmonth,'yyyy-mm'))
),
tmp as(
   select to_date(dt||'-'||mon||'-01','yyyy-mm-dd') dt,cgicode from
   (select lpad(level,2,0) mon from dual connect by level<13 ),
   (select distinct to_char(ym,'yyyy') dt from view1),
   (select distinct cgicode from view1)
   )
   select a.dt,a.cgicode,nvl(b.ncicvtpric,0)  num
   from tmp a full outer join view1 b on a.dt=b.ym and a.cgicode=b.cgicode
   order by a.cgicode,a.dt