本文介绍了计数表中值的连续出现次数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有下表
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.
更多推荐
计数表中值的连续出现次数
发布评论