問題描述

我有這種情況-

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 列中每次出現(xiàn) 1 一樣，我們將 B 列中的數(shù)字增加一.我想在一個選擇中有這個.我不能為此使用循環(huán).

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 和窗口函數(shù) Row_Number()... 但是，我要注意，最好在 OVER 子句中替換 (Select NULL)具有適當(dāng)?shù)捻樞?即身份 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

這篇關(guān)于如何在選擇中增加計數(shù)器的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！