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 1declare @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
用鹤啸九天的解决方法: 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 1select id=identity(int,1,1),* into # from @tselect no,count(col)as '次数' from ( select *,col=(select count(1) from # where id <a.id and no <> a.no) from # a )a group by col,no
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 1declare @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
用鹤啸九天的解决方法:
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 1select id=identity(int,1,1),* into # from @tselect no,count(col)as '次数'
from
(
select
*,col=(select count(1) from # where id <a.id and no <> a.no)
from # a
)a
group by col,no