计算SQL中的连续重复值 [英] Count consecutive duplicate values in SQL

问题描述

我有一个类似的表

ID     OrdID     Value
1      1          0     
2      2          0
3      1          1
4      2          1
5      1          1
6      2          0
7      1          0
8      2          0
9      2          1
10     1          0
11     2          0

我想获取连续计数value，其中值为0。使用上面的示例，结果将为3（行6、7和8）。我正在使用sql server 2008 r2。

I want to get the count of consecutive value where the value is 0. Using the example above the result will be 3 (Rows 6, 7 and 8). I am using sql server 2008 r2.

推荐答案

我假设 id 是唯一的并且在不断增加。您可以通过使用不同的行号来获取连续值的计数。以下将对所有序列进行计数：

I am going to presume that id is unique and increasing. You can get counts of consecutive values by using the different of row numbers. The following counts all sequences:

select grp, value, min(id), max(id), count(*) as cnt
from (select t.*,
             (row_number() over (order by id) - row_number() over (partition by value order by id)
             ) as grp
      from table t
     ) t
group by grp, value;

如果您想要最长的0序列：

If you want the longest sequence of 0s:

select top 1 grp, value, min(id), max(id), count(*) as cnt
from (select t.*,
             (row_number() over (order by id) - row_number() over (partition by value order by id)
             ) as grp
      from table t
     ) t
group by grp, value
having value = 0
order by count(*) desc

这篇关于计算SQL中的连续重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！