分组递增查询.sql

SQL语言常用的一些命令各代码

CPBH             LSH
6666122            0
6666122            0
6666122            0
6666133            0
6666133            0
5454465            0
5451165            0
4541165            0 
4541165            0
5555511            0
我要变用语句变成：
CPBH             LSH
6666122            1
6666122            2
6666122            3 
6666133            1 
6666133            2
5454465            1
5451165            1
4541165            2 
4541165            3
5555511            1

解答1
create table #tab1(cpbh int,lsh int)
insert into #tab1 select 6666122,            0
union all         select 6666122   ,         0
union all         select 6666122    ,       0
union all         select 6666133    ,        0
union all         select 6666133   ,         0
union all         select 5454465   ,         0
union all         select 5451165   ,         0
union all         select 4541165   ,         0 
union all         select 4541165   ,         0
union all         select 5555511   ,         0

declare @table table(cpbh int,id int )
declare @a int ,@b int
select @a=(select min(cpbh) from #tab1)
select @b=1
while @a<=(select max(cpbh) from #tab1)
  begin
      while @b<=(select count(0) from #tab1 where cpbh=@a)
         begin
          insert into @table(cpbh,id)
           values(@a,@b)
          select @b=@b+1
         end
      select @b=1
   select @a=(select min(cpbh) from #tab1 where cpbh>@a)
  end
delete from #tab1
insert into #tab1
select *from @table order by cpbh desc

select * from #tab1

解答二
create table #T
(
 id int,
 col int
)

insert into #T select 6666122,            0
insert into #T select 6666122,            0
insert into #T select 6666122,            0
insert into #T select 6666133,            0
insert into #T select 6666133,            0
insert into #T select 5454465,            0
insert into #T select 5451165,            0
insert into #T select 4541165,            0 
insert into #T select 4541165,            0
insert into #T select 5555511,            0 select * from #T
declare @A int,@B int
update #T set @B = case when @A=id then @B+1 else 1 end,
　　　　　　　col  = @B,
　　　　　　　 @A = id
select * from #T 
解答三

drop table a,test
create  table a (name char(10),num int)
insert into a select '6666122',	0
union all select '6666122',	0
union all select'6666122',	0
union all select'6666133',	0
union all select'6666133',	0
union all select'5454465',	0
union all select'5451165',	0
union all select'4541165',	0
union all select'4541165',	0
union all select'5555511',	0 
 
alter table a add id int IDENTITY
select * from a
go
select t.name ,num=(select count(*) from a where a.id <=t.id and a.name=t.name) into test from a as t
select * from test