我得到了下表,并希望使用sql中前一行的相同列(Column2)的值计算每行的Column2值,而不使用cursor或while循环.
Id Date Column1 Column2 1 01/01/2011 5 5 => Same as Column1 2 02/01/2011 2 18 => (1 + (value of Column2 from the prevIoUs row)) * (1 + (Value of Column1 from the current row)) i.e. (1+5)*(1+2) 3 03/01/2011 3 76 => (1+18)*(1+3) = 19*4 and so on
有什么想法吗？
解决方法
至少假设
recursive CTE的sql Server 2005：
;with cteCalculation as (
select t.Id,t.Date,t.Column1,t.Column1 as Column2
from YourTable t
where t.Id = 1
union all
select t.Id,(1+t.Column1)*(1+c.Column2) as Column2
from YourTable t
inner join cteCalculation c
on t.Id-1 = c.id
)
select c.Id,c.Date,c.Column1,c.Column2
from cteCalculation c
