求一条SQL语句

aqiang1979 2005-07-13 01:42:51
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
...全文
74 点赞 收藏 6
写回复
6 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
davorsuker39 2005-07-13
update #t set identity(int,1,1) as fno,fs,fid,fname from #t order by fs,fname
回复
geniusqing 2005-07-13
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
回复
geniusqing 2005-07-13
不好意思看错了
回复
geniusqing 2005-07-13
--生成数据
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
回复
davorsuker39 2005-07-13
楼主就是这个意思吗?
回复
子陌红尘 2005-07-13
--生成数据
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
回复
发帖
应用实例
创建于2007-09-28

2.7w+

社区成员

MS-SQL Server 应用实例
申请成为版主
帖子事件
创建了帖子
2005-07-13 01:42
社区公告
暂无公告