sql-server – 使用T-SQL中行的先前值计算值

我得到了下表,并希望使用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