基于列值变化的 T-SQL 递增计数器 [英] T-SQL incrementing counter based on change in a column value

问题描述

我有如下示例表.该表按未显示的另一列排序.我需要根据值列的变化来增加计数器列的值.请参阅下面的示例.如何在 T-SQL (SQL Server 2014) 中做到这一点.

I have sample table like below. The table is sorted by another column that is not shown. I need to increment a counter column value based on whenever there is change in the value column. Please see below for an example. How can this be done in T-SQL (SQL Server 2014).

ID Value
1  3
2  3
3  2
4  2
5  2
6  3
7  0
8  0

预期输出:

ID Value Counter
1  3     1
2  3     1
3  2     2
4  2     2
5  2     2
6  3     3
7  0     4
8  0     4

推荐答案

在 SQL Server 2012 及更高版本中，您可以享受 1) 分析函数和 2) 运行总计:

In SQL Server 2012 and later, you have the luxury of 1) analytic functions, and 2) running totals:

declare @t table (
    Id int primary key,
    Value int not null
);

insert into @t (Id, Value)
values
(1, 3),
(2, 3),
(3, 2),
(4, 2),
(5, 2),
(6, 3),
(7, 0),
(8, 0);

select sq.Id, sq.Value,
    sum(case when sq.pVal = sq.Value then 0 else 1 end) over(order by sq.Id) as [Counter]
from (
    select t.Id, t.Value, lag(t.Value, 1, null) over(order by t.Id) as [pVal]
    from @t t
) sq
order by sq.Id;

此外，此解决方案不依赖于 ID 列没有间隙.

Also, this solution does not depend on ID column having no gaps.

这篇关于基于列值变化的 T-SQL 递增计数器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！