求一条SQL语句

[aqiang1979]

fno fs fid fname
12 1 1 a
3 1 2 b
1 1 2 c
4 1 3 d
54 2 1 e
7 2 2 f
78 3 1 g
111 3 1 h
787 3 2 i
要求修改为：
fno fs fid fname
1 1 1 a
2 1 2 b
2 1 2 c
3 1 3 d
4 2 1 e
5 2 2 f
6 3 1 g
6 3 1 h
7 3 2 i

[子陌红尘]

--生成数据
create table #t(fno int,fs int,fid int,fname char(1))
insert into #t select 12 ,1,1,'a'
insert into #t select 3 ,1,2,'b'
insert into #t select 1 ,1,2,'c'
insert into #t select 4 ,1,3,'d'
insert into #t select 54 ,2,1,'e'
insert into #t select 7 ,2,2,'f'
insert into #t select 78 ,3,1,'g'
insert into #t select 111,3,1,'h'
insert into #t select 787,3,2,'i'


--执行更新
declare @i int,@fs int,@fid int
set @i = 0
update
#t
set
@i = (case when fs=@fs and fid=@fid then @i else @i+1 end),
@fs = fs,
@fid = fid,
fno = @i



--输出结果
select * from #t

fno fs fid fname
---- ---- ---- -----
1 1 1 a
2 1 2 b
2 1 2 c
3 1 3 d
4 2 1 e
5 2 2 f
6 3 1 g
6 3 1 h
7 3 2 i

[davorsuker39]

楼主就是这个意思吗？

[geniusqing]

--生成数据
create table #t(fno int,fs int,fid int,fname char(1))
insert into #t select 12 ,1,1,'a'
insert into #t select 3 ,1,2,'b'
insert into #t select 1 ,1,2,'c'
insert into #t select 4 ,1,3,'d'
insert into #t select 54 ,2,1,'e'
insert into #t select 7 ,2,2,'f'
insert into #t select 78 ,3,1,'g'
insert into #t select 111,3,1,'h'
insert into #t select 787,3,2,'i'


--执行更新
select identity(int,1,1) as code ,fs,fid,fname into # from #t
update #t set fno=#.code from # where #t.fs=#.fs and #t.fid=#.fid and #t.fname=#.fname
drop table #
select * from #t

[geniusqing]

不好意思看错了

[geniusqing]

select distinct fs,fid into # from #t
select identity(int,1,1) as code , fs,fid into ## from #
update #t set fno=##.code from ## where #t.fs=##.fs and #t.fid=##.fid
drop table ##
drop table #
select * from #t

[davorsuker39]

update #t set identity(int,1,1) as fno,fs,fid,fname from #t order by fs,fname