Declare @Amount As float=1000
select Id,interval,compound from (values (1,1,0),(2,(3,1),(4,2,(5,(6,3,(7,1)) numbers(Id,compound)
Output:
是否有可能没有光标。.请通过cte / windows函数让我知道。我正在使用Sql Server 2017。
我设法达到预期结果的一种方法是使用while循环。这允许对每组值进行调整计算。这是我尝试窗口函数时遇到的最大问题，该函数对于隔离每个间隔的求和非常有用，但对于根据先前值的总和进行计算却不那么理想。我在递归公用表表达式中遇到了类似的问题，在该表达式中，计算将在结果集上进行，但不会考虑上一个时间间隔的值。
Declare @Amount As float=1000;
declare @currentinterval int = 1;
declare @schedule table (Id int,interval int,Compound bit);
insert @schedule select Id,interval,compound from (values (1,1,0),(2,(3,1),(4,2,(5,(6,3,(7,(8,5,(9,6,(10,7,1)) numbers(Id,compound);
declare @output table (interval int,balance float,interest float);
declare @stoppingpoints table (Id int);
insert @stoppingpoints
select Id from @schedule where Compound=1;
while exists (select 1 from @stoppingpoints)
begin
if @currentinterval = 1
begin
insert @output (interval,balance,interest)
select @currentinterval,@Amount,round(@Amount/cast(100 as float),1) from @schedule where Id <= (select min(Id) from @stoppingpoints);
end
if @currentinterval>1
begin
insert @output (interval,interest)
select @currentinterval,balance + interesttotal,round((balance + interesttotal) / cast(100 as float),1) from @schedule s inner join
(select interval+1 as interval,sum(interest) as interesttotal from @output where interval = @currentinterval-1 group by interval,balance) o
on s.interval>=o.interval where s.Id <= (select min(Id) from @stoppingpoints);
end
delete top (1) from @stoppingpoints;
set @currentinterval = @currentinterval + 1;
end
select * from @output;