如何在T-SQL中创建特定值的计数器？

Question

我有一个问题已经苦思冥想了几个小时。我正在尝试在临时表的一列上创建一个特殊计数器，我的表有很多列，其中之一是col1：

col1 |
######
X    | 
X    | 
A    | 
B    | 
X    | 
C    | 
X    | 
D    | 

特殊计数器应该是这样的：

col1 | counter
###############
X    | 1
X    | 2
A    | 2
B    | 2
X    | 3
C    | 3
X    | 4
D    | 4

它应该仅计算“X”值，并在值为其他内容时保持不变。

我尝试了很多方法，最接近的是通过创建另一个仅包含计数器的临时表，然后将其与原始表连接，但结果如下：

col1 | counter
###############
X    | 1
X    | 2
A    | NULL
B    | NULL
X    | 3
C    | NULL
X    | 4
D    | NULL

那么，我该如何创建这个特殊的计数器呢？

- Abeer Sul

---

你按哪个列排序？使用的是哪个版本的SQL Server？ - Dan Field

@DanField，临时表中还有另一个SortOrder列，我正在使用它来排序值，但在问题中没有显示。 - Abeer Sul

Answer 1

Row_Number() 在 CASE 中可以解决问题。

您会注意到我添加了一个ID字段来维护序列。

Declare @YourTable table (ID int,col1 varchar(25))
Insert Into @YourTable values
(1,'X'),
(2,'X'),
(3,'A'),
(4,'B'),
(5,'X'),
(6,'C'),
(7,'X'),
(8,'D')

Select ID,Col1
      ,Counter = max(counter) over (Order By ID)
 From (
       Select ID
             ,col1
             ,counter = case when col1='X' then row_number() over (Partition By col1 Order by ID) else null end
        From  @YourTable
      ) A
 Order By ID

返回

ID  Col1    Counter
1   X       1
2   X       2
3   A       2
4   B       2
5   X       3
6   C       3
7   X       4
8   D       4

- John Cappelletti

---

实际上我在我的问题中提到了我不想要NULL值，请看最后的结果表，计数器在这种情况下应该保持不变。 - Abeer Sul

@AbeerSul 误解了问题。我会再试一次。 - John Cappelletti

@AbeerSul 是否还有另一列包含序列？ - John Cappelletti

你能解释一下这是怎么工作的吗？我不太理解。 - Abeer Sul

@AbeerSul 子查询中的计数器将为 X 生成一个 row_number()。然后，max(counter) 是另一个窗口函数。它将从当前行和前面的行中获取最大的 X-Counter。窗口函数是您的好朋友。花时间熟悉它们是非常值得的。 - John Cappelletti

Answer 2

以下是更新语句。该语句在每次遇到“X”时增加变量，并使用正确的值更新每一行。

declare @counter int = 0;
update   #temp
set      counter = @counter
       , @counter += case when col1 = 'X' then 1
                          else 0
                     end;

- A.J. Schroeder

---

我不知道你可以在更新的设置部分更新标量和列。很酷。 - Dan Field

我唯一要补充的是，这可能不会按照 OP 想要的排序列进行排序。 - Dan Field

Answer 3

有一个更简单和直接的解决方案。我们只需要稍微观察一下：计数器等于前面行中 X 的数量：

select id, 
       col1,
       (select count(*) from @t where id <= t.id and col1 = 'X') as counter
from @t t

- Giorgi Nakeuri