计数表中值的连续出现次数
我有下表
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.
相关文章