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

I got following table and want to calculate value of Column2 on each row using the value of the same column (Column2) from the previous row in a sql without using cursor or while loop.

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

Any thoughts?

有什么想法吗？

4 个解决方案

#1

Assuming at least SQL Server 2005 for the recursive CTE:

假设至少SQL Server 2005用于递归CTE：

;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, t.Date, t.Column1, (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

#2

I solved the problem, just mentioned.

我刚刚提到，我解决了这个问题。

This is my code:

这是我的代码：

;with cteCalculation as (
    select t.Id, t.Column1, t.Column1 as Column2
        from table_1 t
        where t.Id = 1
    union all
    select t.Id, t.Column1, (1+t.Column1)*(1+c.Column2) as Column2
        from table_1 t
            inner join cteCalculation c
                on t.Id-1 = c.id
),
cte2 as(
select t.Id, t.Column1 as Column3
        from table_1 t
        where t.Id = 1
    union all
    select t.Id, (select column2+1 from cteCalculation c where c.id = t.id)  as Column3
        from table_1 t
            inner join cte2 c2
                on t.Id-1 = c2.id
)

select c.Id, c.Column1, c.Column2, c2.column3
    from cteCalculation c
inner join cte2 c2 on c.id = c2.id

The result is as I was expected:

结果如我所料：

1           5   5   5
2           2   18  19
3           3   76  77

#3

Here is an example using ROW_NUMBER() if the Id's aren't necessarily in order:

以下是使用ROW_NUMBER（）的示例，如果Id不一定按顺序排列：

;with DataRaw as (
    select 1 as Id, '01/01/11' as Date, 5 as Column1 union
    select 2 as Id, '02/01/11' as Date, 2 as Column1 union
    select 4 as Id, '03/01/11' as Date, 3 as Column1
),
Data as (
    select RowId = ROW_NUMBER() over (order by Id), Id, Date, Column1 from DataRaw
),
Data2 as (
    select
        RowId, id, Date, Column1, Column1 as Column2
    from
        Data d
    where
        RowId = 1

    union all

    select
        d1.RowId, d1.id, d1.Date, d1.Column1, (1+d1.column1)*(1+d2.column2) as column2
    from
        Data d1
    cross join
        Data2 d2
    where
        d2.RowId + 1 = d1.RowId
)
select
    Id, Date, Column1, Column2
from
    Data2

#4

-1

edit: shoudld have read the question better...

编辑：shoudld已经更好地阅读了这个问题......

Another go woudl be this:

另一个是这样的：

;with DataRaw as (
    select 1 as Id, '01/01/11' as Date, 5 as Column1 union
    select 2 as Id, '02/01/11' as Date, 2 as Column1 union
    select 4 as Id, '03/01/11' as Date, 3 as Column1
),
Data as (
    select Ord = ROW_NUMBER() over (order by Id), Id, Date, Column1 from DataRaw
),
select -- formula goes here, using current and prev as datasources.
from  data current
      left join data prev on current.Ord = prev.Ord + 1  -- pick the previous row by adding 1 to the ordinal

I think a normal join to get to the previous row would be faster than a CTE. You;d have to check for yourself though.

我认为到达前一行的正常连接比CTE更快。你必须自己检查一下。

Looks easier to me.

对我来说更容易。

Good luck, GJ

祝你好运，GJ

#1