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
...全文
996打赏收藏
求一条SQL语句
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
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
--生成数据
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