SQL语句求优化

[ybren99]

select changeVal.val ,val.results,changeVal.changeTime,val.changeNum from (select sum(pur.purchase_change_cost) val,count(pur.sp_code) changeTime
  from ba_netmessage_purchase pur
  left join ba_sp_info sp on pur.sp_code = sp.sp_code
  left join ba_area area on pur.area_code = area.AREA_CODE   
 where pur.sp_code in
       (select t12.sp_code
          from (select ba.purchase_lowest_cost, ba.sp_code
                  from ba_netmessage_purchase ba,
                       (select max(oprate_date) oprate_date, sp_code
                          from ba_netmessage_purchase
                         group by sp_code)  t1
                 where ba.sp_code = t1.sp_code
                   and ba.oprate_date = t1.oprate_date) t12,
               (select ba.purchase_lowest_cost, ba.sp_code
                  from ba_netmessage_purchase ba,
                       (select min(oprate_date) oprate_date, sp_code
                          from ba_netmessage_purchase
                         group by sp_code)  t1
                 where ba.sp_code = t1.sp_code
                   and ba.oprate_date = t1.oprate_date) t2
         where t12.sp_code = t2.sp_code
           and t12.purchase_lowest_cost - t2.purchase_lowest_cost > 0)  
   and pur.oprate_date between
       to_date('2012-1-19 00:00:00', 'yyyy-MM-dd HH24:MI:SS') AND
       to_date('2012-12-19 23:59:59', 'yyyy-MM-dd HH24:MI:SS')
   AND pur.area_code = '100'  ) changeVal,--and sp.sp_name like '%%'
       ( select sum(t12.purchase_lowest_cost)results,count(t12.sp_code) changeNum
          from  (select ba.purchase_lowest_cost, ba.sp_code,ba.purchase_change_cost
                  from ba_netmessage_purchase ba,
                       (select max(ba.oprate_date) oprate_date, ba.sp_code
                          from ba_netmessage_purchase ba left join ba_sp_info sp on ba.sp_code=sp.sp_code 
                          where  ba.oprate_date between
                    to_date('2012-1-1 00:00:00', 'yyyy-MM-dd HH24:MI:SS') AND
                      to_date('2012-12-18 23:59:59', 'yyyy-MM-dd HH24:MI:SS')
                            AND ba.area_code = '100'  -- and sp.sp_name like '%%'
                         group by ba.sp_code)  t1
                 where ba.sp_code = t1.sp_code
                   and ba.oprate_date = t1.oprate_date  
                    ) t12,
               (select ba.purchase_lowest_cost, ba.sp_code
                  from ba_netmessage_purchase ba,
                       (select min(oprate_date) oprate_date, sp_code
                          from ba_netmessage_purchase
                         group by sp_code)  t1
                 where ba.sp_code = t1.sp_code
                   and ba.oprate_date = t1.oprate_date) t2
         where t12.sp_code = t2.sp_code
           and t12.purchase_lowest_cost - t2.purchase_lowest_cost > 0 ) val;

请各位帮忙从语法上优化下，对SQL不精通!

[啊彪123]

靠，这么多。不慢才怪！分步啊。

[wflyxiaonian]

这语句能写出来 也就会优化了

[半亩方塘立身]

太多了，建议你分块进行优化，或者考虑建立视图、存储过程啥的

[jdsnhan]

贴执行计划