本文介绍了如何在选择中增加计数器的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!
问题描述
我有这种情况-
Column A
1
0
0
0
1
0
0
0
0
1
0
1
0
0
1
0
我想要这样的东西-
Column A Column B
1 1
0 1
0 1
0 1
1 2
0 2
0 2
0 2
0 2
1 3
0 3
1 4
0 4
0 4
1 5
0 5
就像在 A 列中每次出现 1 一样,我们将 B 列中的数字增加一.我想在一个选择中有这个.我不能为此使用循环.
Its like for each occurance of 1 in column A we are increasing the number in column B by one. I want to have this in a select. I can't use loop for this.
我使用的是 SQL-Server 2008 R2.任何人都可以请告诉我它是如何做到的.提前致谢.
I am using SQL-Server 2008 R2. Can anyone please give me idea how it can done. Thanks in advance.
推荐答案
使用 cte 和窗口函数 Row_Number()... 但是,我要注意,最好在 OVER 子句中替换 (Select NULL)具有适当的顺序(即身份 int、日期时间).
With a cte and window function Row_Number()... However, I should note that it would be best if you replace (Select NULL) in the OVER clause with a proper sequence (ie identity int, datetime).
Declare @YourTable table (ColumnA int)
Insert Into @YourTable values (1),(0),(0),(0),(1),(0),(0),(0),(0),(1),(0),(1),(0),(0),(1),(0)
;with cte as (
Select *,RN=Row_Number() over (Order By (Select Null)) from @YourTable
)
Select A.ColumnA
,ColumnB = sum(B.ColumnA)
From cte A
Join cte B on (B.RN<=A.RN)
Group By A.ColumnA,A.RN
Order By A.RN
退货
ColumnA ColumnB
1 1
0 1
0 1
0 1
1 2
0 2
0 2
0 2
0 2
1 3
0 3
1 4
0 4
0 4
1 5
0 5
这篇关于如何在选择中增加计数器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!