统计相邻相同行数量
A
A
A
B
B
A
C
C
B
结果:
A 3
B 2
A 1
C 2
B 2
A
A
B
B
A
C
C
B
结果:
A 3
B 2
A 1
C 2
B 2
作者: xiaowangtian1117 发布时间: 2011-06-10
友情帮顶
作者: chuanzhang5687 发布时间: 2011-06-10
友情帮顶
作者: l3143344 发布时间: 2011-06-10
SQL code
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 行受影响) **/
作者: FlySQL 发布时间: 2011-06-10