求助SQL逐行迭代计算问题

[kawashimawind]

我的表结构是这样的
序号 基本价格 增长率 费率
1 1000 1.001909737 1.70333E-05
2 1000 1.004233607 0.0000365
3 1000 1.00409676 0.0000395
4 1000 1.004233607 0.0000395
5 1000 1.004233607 0.0000395
6 1000 1.00409676 0.0000395
7 1000 1.004233607 0.0000395
8 1000 1.00409676 0.0000395
9 1000 1.004233607 0.0000395
10 1000 1.004233607 0.0000395
11 1000 1.003823121 0.0000395
12 1000 1.004233607 0.0000395
13 1000 1.00409676 0.0000395
14 1000 1.004233607 0.0000395
15 1000 1.00409676 0.0000425
16 1000 1.004233607 0.0000425
17 1000 1.004233607 0.0000425
18 1000 1.00409676 0.0000425
19 1000 1.004233607 0.0000425
20 1000 1.00409676 0.0000425
...

我需要加入三列：初始价格、费用、最终价格
第一行的初始价格是“基本价格”*0.98，也就是980，之后每行初始价格是上一行的最终价格
费用 是每行 round(初始价格*费率,2)，最终价格 是 round(初始价格*增长率-费用,2)
具体结果是（用Excel计算结果）

序号 基本价格 增长率 费率 初始价格 费用 最终价格
1 1000 1.001909737 1.70333E-05 980 0.02 981.85
2 1000 1.004233607 0.0000365 981.85 0.04 985.97
3 1000 1.00409676 0.0000395 985.97 0.04 989.97
4 1000 1.004233607 0.0000395 989.97 0.04 994.12
5 1000 1.004233607 0.0000395 994.12 0.04 998.29
6 1000 1.00409676 0.0000395 998.29 0.04 1002.34
7 1000 1.004233607 0.0000395 1002.34 0.04 1006.54
8 1000 1.00409676 0.0000395 1006.54 0.04 1010.62
9 1000 1.004233607 0.0000395 1010.62 0.04 1014.86
10 1000 1.004233607 0.0000395 1014.86 0.04 1019.12
11 1000 1.003823121 0.0000395 1019.12 0.04 1022.98
12 1000 1.004233607 0.0000395 1022.98 0.04 1027.27
13 1000 1.00409676 0.0000395 1027.27 0.04 1031.44
14 1000 1.004233607 0.0000395 1031.44 0.04 1035.77
15 1000 1.00409676 0.0000425 1035.77 0.04 1039.97
16 1000 1.004233607 0.0000425 1039.97 0.04 1044.33
17 1000 1.004233607 0.0000425 1044.33 0.04 1048.71
18 1000 1.00409676 0.0000425 1048.71 0.04 1052.97
19 1000 1.004233607 0.0000425 1052.97 0.04 1057.39
20 1000 1.00409676 0.0000425 1057.39 0.04 1061.68
...

[nayi_224]

create table ty_1106(id number, jb number, zz number, fl number);

with t0(id, jb, zz, fl, chushi, fy, fin) as (
select t1.id,
       t1.jb,
       t1.zz,
       t1.fl,
       t1.jb * 0.98,
       round(t1.jb * 0.98 * t1.fl, 2),
       round(t1.jb * 0.98 * t1.zz - round(t1.jb * 0.98 * t1.fl, 2), 2)
  from ty_1106 t1 where t1.id = 1
union all
select t1.id,
       t1.jb,
       t1.zz,
       t1.fl,
       t0.fin,
       round(t0.fin * t1.fl, 2),
       round(t0.fin * t1.zz - round(t0.fin * t1.fl, 2), 2)
  from ty_1106 t1, t0 t0 where t1.id = t0.id + 1
)
select*from t0;

[stelf]

先试试递归吧，看这样是否满足你的需求。 with mt as (select 1 as id, 1000 as bas_price, 1.001909737 as incr_rate, 0.0000170333 as fee_rate from dual union all select 2, 1000, 1.004233607, 0.0000365 as fee_rate from dual union all select 3, 1000, 1.00409676, 0.0000395 as fee_rate from dual union all select 4, 1000, 1.004233607, 0.0000395 as fee_rate from dual union all select 5, 1000, 1.004233607, 0.0000395 as fee_rate from dual), tmp(id, bas_price, incr_rate, fee_rate, initial_price, fee, final_price) as (select mt.id as id, mt.bas_price as bas_price, mt.incr_rate as incr_rate, mt.fee_rate as fee_rate, mt.bas_price * 0.98 as initial_price, round(mt.bas_price * 0.98 * mt.fee_rate, 2) as fee, round(mt.bas_price * 0.98 * (mt.incr_rate - mt.fee_rate), 2) as final_price from mt where mt.id = 1 union all select mt.id, mt.bas_price, mt.incr_rate, mt.fee_rate, tmp.final_price, round(tmp.final_price * mt.fee_rate, 2), round(tmp.final_price * (mt.incr_rate - mt.fee_rate), 2) from tmp inner join mt on tmp.id + 1 = mt.id where tmp.id < 5) select * from tmp;

[kawashimawind]

感谢两位老师，完美解决了我这个SQL新手的问题