表中数值连续出现的次数 [英] Count Number of Consecutive Occurrence of values in Table

问题描述

我在表下面

create table #t (Id int, Name char)

insert into #t values
(1, 'A'),
(2, 'A'),
(3, 'B'),
(4, 'B'),
(5, 'B'),
(6, 'B'),
(7, 'C'),
(8, 'B'),
(9, 'B')

我想计算名称列中的连续值

I want to count consecutive values in name column

+------+------------+
| Name | Repetition |
+------+------------+
| A    |          2 |
| B    |          4 |
| C    |          1 |
| B    |          2 |
+------+------------+

我尝试过的最好的方法是：

The best thing I tried is:

select Name
, COUNT(*) over (partition by Name order by Id) AS Repetition
from #t
order by Id

但这并不能给我预期的结果

but it doesn't give me expected result

推荐答案

一种方法是行号的不同：

One approach is the difference of row numbers:

select name, count(*) 
from (select t.*,
             (row_number() over (order by id) -
              row_number() over (partition by name order by id)
             ) as grp
      from t
     ) t
group by grp, name;

如果运行子查询并分别查看每个行号的值，则逻辑最容易理解然后看一下区别。

The logic is easiest to understand if you run the subquery and look at the values of each row number separately and then look at the difference.

这篇关于表中数值连续出现的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！