统计连续字段的个数

问题要求
declare @t table(no int)
insert into @t
select 2
union all select 2
union all select 2
union all select 1
union all select 2
union all select 4
union all select 4
union all select 1
union all select 1
想要的答案是 计算出连续的一样no的个数
count_no
3
1
1
2
2

方法：

declare @t table(no int)
insert into @t
select 2
union all select 2
union all select 2
union all select 1
union all select 2
union all select 4
union all select 4
union all select 1
union all select 1

declare @tb table(no1 int)

declare @a varchar(8000),@b int,@c varchar(2)
set @b=1
set @a=”
set @c=”
select @a=@a+’,'+cast(no as varchar(20)) from @t
while(len(@a)>0)
begin
if (@c=”)
begin
set @c=substring(@a,2,1)
set @a=stuff(@a,1,2,”)
end
else
begin
if(@c=substring(@a,2,1))
begin
set @a=stuff(@a,1,2,”)
set @b=@b+1
end
else
begin
set @c=”
insert into @tb select @b
set @b=1
end
end
end
insert into @tb select @b
select * from @tb

no1
———–
3
1
1
2
2