blocks|key|2228501|text|假设至少Server+2005用于递归CTE|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2228502|;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%2Bt.Column1)*(1%2Bc.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|code-block|syntax|javascript|2228503|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms186243.aspx^0|H|5|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Assuming at least SQL Server 2005 for the <a href="http://msdn.microsoft.com/en-us/library/ms186243.aspx" rel="noreferrer">recursive CTE</a>:

<pre><code>;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
</code></pre>

blocks|key|1031383|text|下面是一个使用ROW_NUMBER()的示例，如果Id不一定按顺序排列：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1031384|;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%2Bd1.column1)*(1%2Bd2.column2)+as+column2
++++from
++++++++Data+d1
++++cross+join
++++++++Data2+d2
++++where
++++++++d2.RowId+%2B+1+=+d1.RowId
)
select
++++Id,+Date,+Column1,+Column2
from
++++Data2|code-block|syntax|javascript|1031385|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>;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
</code></pre>

blocks|key|1031417|text|编辑:应该把问题读得更清楚.|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1031418|另一个理由是：|1031419|;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+%2B+1++--+pick+the+previous+row+by+adding+1+to+the+ordinal|code-block|syntax|javascript|1031420|我认为正常连接到上一行的速度要比CTE快。不过，你得自己检查一下。|1031421|在我看来比较容易。|1031422|祝你好运，GJ|1031423|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

edit: shoudld have read the question better...

Another go woudl be this:

<pre><code>;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
</code></pre>

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. 

Looks easier to me. 

Good luck, GJ

blocks|key|2587696|text|我解决了问题刚才提到了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2587697|这是我的密码：|2587698|;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%2Bt.Column1)*(1%2Bc.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%2B1+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|code-block|syntax|javascript|2587699|结果如我所料：|2587700|1+++++++++++5+++5+++5
2+++++++++++2+++18++19
3+++++++++++3+++76++77|2587701|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I solved the problem, just mentioned.

This is my code: 

<pre><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
</code></pre>

The result is as I was expected: 

<pre><code>1 5 5 5
2 2 18 19
3 3 76 77
</code></pre>

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.

<pre><code>Id Date Column1 Column2
1 01/01/2011 5 5 =&gt; Same as Column1
2 02/01/2011 2 18 =&gt; (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 =&gt; (1+18)*(1+3) = 19*4
and so on
</code></pre>

Any thoughts?

<hr>

Calculating value using previous value of a row in T-SQL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我得到了下表，并希望在不使用游标或while循环的情况下，使用来自sql中前一行的相同列( Column2 )的值计算每一行的值。Id   Date             Column1    Column21    01/01/2011       5          5 => Same as Column12 ...

问使用T中行的前一个值计算值
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用T中行的前一个值计算值EN