SQL语句求优化
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;