sql优化-总结

[sql] 
select month_id,  
       corppkno,  
       sum(exportSum_new) exportSum_new,  
       sum(exportSum_newLy) exportSum_newLy,  
       sum(exportSum_Support) exportSum_Support,  
       sum(exportSum_SupportLy) exportSum_SupportLy  
  from ( /*当年累计出口*/  
        select a.month_id,  
                c.corppkno,  
                decode(a.isnewinsurant, null, 0, b.exportdollar) exportSum_new,/*此处为了用一条sql实现*/  
                0 exportSum_newLy,  
                b.exportdollar exportSum_Support,  
                0 exportSum_SupportLy  
          from (select trunc(t1.month_id / 100) yearid,  
                        t1.month_id,  
                        t3.cocode,  
                        max(t.newinsurantpkno_sm) isnewinsurant /*当月新增的客户*/  
                   from stdw.F_Sum_SupportInsurant_SM     t,  
                        stdw.lu_month_cumulate            t1,  
                        stdw.d_t_customer                 t2,  
                        stdw.d_t_Customsenterprisemapping t3  
                  where t.monthid = t1.month_cumul_id  
                    and t.supportinsuantpkno_sm = t2.pkno  
                    and t2.crmno = t3.customno  
                    and t3.state = '1'  
                    and t1.month_id <= to_char(sysdate - 1, 'YYYYMM')  
                  group by t1.month_id, t3.cocode) A,  
                stdw.f_custom_company_composite B,  
                stdw.d_custom_branch_province C,  
                stdw.lu_month_cumulate D /*此sql先用子查询A限定范围，再通过A去关联B。因为B的范围大，如果对B进行汇总后再和A关联，效率较低*/  
         where b.monthid = d.month_cumul_id  
           and b.corpid = c.corpid  
           and a.yearid = b.yearid /*跨区访问*/  
           and a.month_id = d.month_id  
           and a.cocode = b.cocode  
        union all  
        /*上年总出口额*/  
        select a.month_id,  
               b.corppkno,  
               0 exportSum_new,  
               decode(a.isnewinsurant, null, 0, b.exportdollar) exportSum_newLy,  
               0 exportSum_Support,  
               b.exportdollar exportSum_SupportLy  
          from (select trunc(t1.month_id / 100) - 1 yearid_ly,  
                       t1.month_id,  
                       t3.cocode,  
                       max(t.newinsurantpkno_sm) isnewinsurant /*当月新增的客户*/  
                  from stdw.F_Sum_SupportInsurant_SM     t,  
                       stdw.lu_month_cumulate            t1,  
                       stdw.d_t_customer                 t2,  
                       stdw.d_t_Customsenterprisemapping t3  
                 where t.monthid = t1.month_cumul_id  
                   and t.supportinsuantpkno_sm = t2.pkno  
                   and t2.crmno = t3.customno  
                   and t3.state = '1'  
                   and t1.month_id <= to_char(sysdate - 1, 'YYYYMM')  
                 group by t1.month_id, t3.cocode) A,  
               (select t1.outputyear  yearid,  
                       t1.cocode,  
                       t4.corppkno,  
                       t1.totaldollar exportdollar  
                  from stdw.f_custom_company_total   t1,  
                       stdw.d_custom_company         t2,  
                       stdw.d_custom_province_zone   t3,  
                       stdw.d_custom_branch_province t4  
                 where t1.cocode = t2.cocode  
                   and t2.zonecode = t3.zone  
                   and t3.province_no = t4.proviceid) B  
         where a.yearid_ly = B.yearid  
           and a.cocode = B.cocode)  
 group by month_id, corppkno