数据表如下:
aa bb
a1 ww
a1 ww
a1 ww
a2 ww
a2 ww
a3 ww
现在想把数据修改为:
aa bb
a1 ww1
a1 ww2
a1 ww3
a2 ww1
a2 ww2
a3 ww1
如何修改??
------解决方案--------------------
- SQL code
create table #a(aa char(3),bb char(3)) insert into #a values( 'a1','ww' ) insert into #a values( 'a1','ww' ) insert into #a values( 'a1','ww' ) insert into #a values( 'a2','ww' ) insert into #a values( 'a2','ww' ) insert into #a values( 'a2','ww' ) insert into #a values( 'a3','ww' ) insert into #a values( 'a3','ww' )select id=Identity(int,1,1) ,*,0 as bz into #b from #a declare @aa char(3),@i int ,@count int,@max int,@j intselect @i = 1,@j =0,@max =0 while @i < 10 begin select @aa = aa from #b where id = @i select @max = max(id) from #b where aa = @aa print @aa print @max set @j = @j + 1 update #b set bz = @j where id = @i if @i = @max begin set @j =0 end set @i= @i +1 endselect * From #bdrop table #bdrop table #a