计数表中值的连续出现次数

我有下表

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

推荐答案

一种方法是行号不同:

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.

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