统计相邻相同行数量

[xiaowangtian1117]

A
A
A
B
B
A
C
C
B
结果：
A 3
B 2
A 1
C 2
B 2

[chuanzhang5687]

友情帮顶

[l3143344]

友情帮顶

[FlySQL]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(1))
insert [tb]
select 'A' union all
select 'A' union all
select 'A' union all
select 'B' union all
select 'B' union all
select 'A' union all
select 'C' union all
select 'C' union all
select 'B'
 
;with fly as
(select *,rn=row_number() over(order by getdate()) from tb)

select col,count(1) as cnt
from(
select col,rn,
gid=rn-(select count(1) from fly where col=t.col and rn<t.rn)
from fly t
) a
group by col,gid
order by min(rn)
/**
col  cnt
---- -----------
A    3
B    2
A    1
C    2
B    1

(5 行受影响)

**/