sql 后一笔数据由 前几笔数据计算出来解决方案
sql 后一笔数据由 前几笔数据计算出来
表A
ID Pay PlanPay
1 200 1000
2 100 1000
3 300 1000
4 200 1000
5 200 1000
想由A得到下表
ID Pay PlanPay unPay
1 200 1000 1000
2 100 1000 800
3 300 1000 700
4 200 1000 400
5 200 1000 200
unPay字段值= PlanPay-上几笔的Pay总值
第1笔 : 1000-0=1000;
第2笔:1000-200=800;
第3笔:1000-200-100=700;
第4笔:1000-200-100-300=400;
第4笔:1000-200-100-300-200=200;
不知道大家有没有好的什么办法可以实现这样的
------解决方案--------------------
------解决方案--------------------
------解决方案--------------------
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
表A
ID Pay PlanPay
1 200 1000
2 100 1000
3 300 1000
4 200 1000
5 200 1000
想由A得到下表
ID Pay PlanPay unPay
1 200 1000 1000
2 100 1000 800
3 300 1000 700
4 200 1000 400
5 200 1000 200
unPay字段值= PlanPay-上几笔的Pay总值
第1笔 : 1000-0=1000;
第2笔:1000-200=800;
第3笔:1000-200-100=700;
第4笔:1000-200-100-300=400;
第4笔:1000-200-100-300-200=200;
不知道大家有没有好的什么办法可以实现这样的
------解决方案--------------------
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
------解决方案--------------------
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