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

本文介绍了计数表中值的连续出现次数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有下表

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.