统计相邻相同行数量

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 行受影响)



**/