declare @test table(Period int, Amt float)declare @amt float =120 declare @period int = 12 insert into @test(Period,Amt) values(1, @amt-@amt/@period)declare @i int =1 while @i<@period begin set @i = @i+1 insert into @test(Period,Amt) values(@i,@amt-@i*@amt/@period) endselect * from @testPeriod Amt 1 110 2 100 3 90 4 80 5 70 6 60 7 50 8 40 9 30 10 20 11 10 12 0
mysql8.0以后可以直接用递归WITH RECURSIVE cte (p_amt, p_int) AS ( SELECT 120, 0 from dual UNION ALL SELECT p_amt - 10, p_int + 1 FROM cte WHERE cte.p_int < 12 ) SELECT * FROM cte where cte.p_int > 0;
declare @period int = 12
insert into @test(Period,Amt) values(1, @amt-@amt/@period)declare @i int =1
while @i<@period
begin
set @i = @i+1
insert into @test(Period,Amt) values(@i,@amt-@i*@amt/@period)
endselect * from @testPeriod Amt
1 110
2 100
3 90
4 80
5 70
6 60
7 50
8 40
9 30
10 20
11 10
12 0
(
SELECT 120, 0 from dual
UNION ALL
SELECT p_amt - 10, p_int + 1 FROM cte WHERE cte.p_int < 12
)
SELECT * FROM cte where cte.p_int > 0;