求和SQL

select main.* ,nvl((select nvl(conversion_rate,1)
from icas_base_daily_rates_temp
where from_currency=main.org_currency and upper(to_currency)='USD'
and conversion_date=to_date('2009-9-10', 'YYYY-MM-DD')),1) rate
from
(select id,total,amount,
(case when total<=3 then amount
else (3-last_total)/nvl((select nvl(conversion_rate,1)
from icas_base_daily_rates_temp
where from_currency=currencycode and upper(to_currency)='USD'
and conversion_date=to_date('2009-9-10', 'YYYY-MM-DD')),1)
end) amount_t,
currencycode org_currency
from
(select a.id,
SUM(a.amount_usd) OVER(ORDER BY a.amount_usd, a.id) as total ,
SUM(a.amount_usd) OVER(ORDER BY a.amount_usd, a.id)-a.amount_usd as last_total ,
a.amount,a.currencycode
from
(select m.amount *nvl(t.conversion_rate,1) amount_usd ,
m.amount,id,m.currencycode
from icas_base_daily_rates_temp t,
t_money m
where m.currencycode=t.from_currency
and upper(t.to_currency)='USD'
and t.conversion_date=to_date('2009-9-10', 'YYYY-MM-DD'))a)
where total<= (select min(total) from
(select id,total from
(select a.id,
SUM(a.amount_usd) OVER(ORDER BY a.amount_usd, a.id) as total
from
(select m.amount *nvl(t.conversion_rate,1) amount_usd ,
m.amount,id
from icas_base_daily_rates_temp t,
t_money m
where m.currencycode=t.from_currency
and upper(t.to_currency)='USD'
and t.conversion_date=to_date('2009-9-10', 'YYYY-MM-DD'))a)
where total>=3))) main