本文介绍了计数表中值的连续出现次数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有下表
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.
这篇关于计数表中值的连续出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！