select t.*, unPay = t.planpay - (select isnull(sum(e.pay),0) from tba e on e.id < t.id) from tba t
with tb(ID,Pay,PlanPay)as( select 1,200,1000 from dual union select 2,100,1000 from dual union select 3,300,1000 from dual union select 4,200,1000 from dual union select 5,200,1000 from dual) select id,pay,planpay, nvl(planpay-(select sum(pay) from tb where t.id>id),planpay) unpay from tb t
看差,以为是MSSQL版的,isnull改nvl 。
with tb(ID,Pay,PlanPay)as( select 1 ,200,1000 from dual union select 2,100,1000 from dual union select 3,300,1000 from dual union select 4,200,1000 from dual union select 5,200,1000 from dual)select id,pay,planpay, planpay + pay - sum(pay) over( order by id) from tb
select t.*,
unPay = t.planpay - (select isnull(sum(e.pay),0) from tba e on e.id < t.id)
from tba t
with tb(ID,Pay,PlanPay)as(
select 1,200,1000 from dual union
select 2,100,1000 from dual union
select 3,300,1000 from dual union
select 4,200,1000 from dual union
select 5,200,1000 from dual)
select id,pay,planpay,
nvl(planpay-(select sum(pay) from tb where t.id>id),planpay) unpay
from tb t
看差,以为是MSSQL版的,isnull改nvl 。
select 1 ,200,1000 from dual union
select 2,100,1000 from dual union
select 3,300,1000 from dual union
select 4,200,1000 from dual union
select 5,200,1000 from dual)select id,pay,planpay, planpay + pay - sum(pay) over( order by id)
from tb