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不精通!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货