使用T中行的前一个值计算值
使用T中行的前一个值计算值
提问于 2011-02-23 16:23:46
我得到了下表，并希望在不使用游标或while循环的情况下，使用来自sql中前一行的相同列( Column2 )的值计算每一行的值。
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有什么想法吗？
回答 4
Stack Overflow用户
发布于 2011-02-23 16:42:28
假设至少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 cStack Overflow用户
发布于 2011-06-17 11:51:23
我解决了问题刚才提到了。
这是我的密码：
;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结果如我所料：
1 5 5 5
2 2 18 19
3 3 76 77Stack Overflow用户
发布于 2011-02-23 16:53:35
下面是一个使用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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/5093682
复制相关文章
相似问题
腾讯云开发者
