create table t( id int, name varchar(10) ) insert into t select 2,'ee' union all select 2,'rr' union all select 7,'xx' union all select 9,'ss' union all select 17,'ww'select identity(int,1,1) as [id],[name] into #t from t select * from #tupdate t set id=a.id from #t a where a.name=t.name select * from t drop table #t drop table t
alter table t1 with nocheck add sn int identity(1,1); update t1 set id=sn; alter table t1 drop column sn;
update tab set tab.id = tt.id2 from ( select id2 = (select count(1) from tab where id < a.id or (id = a.id and name = a.name)),id,name from tab a )tt where tab.id = tt.id and tab.name = tt.name
create table t( id int, name varchar(10) ) insert into t select 2,'ee' union all select 2,'rr' union all select 7,'xx' union all select 9,'ss' union all select 17,'ww' --创建临时表 select identity(int,1,1) as [id],[name] into #t from t --更新 update t set id=a.id from #t a where a.name=t.name select * from t --删除表 drop table #t drop table t
id int,
name varchar(10)
)
insert into t
select 2,'ee'
union all select 2,'rr'
union all select 7,'xx'
union all select 9,'ss'
union all select 17,'ww'select identity(int,1,1) as [id],[name] into #t from t select * from #tupdate t set id=a.id from #t a where a.name=t.name
select * from t
drop table #t
drop table t
update t1 set id=sn;
alter table t1 drop column sn;
from
(
select id2 = (select count(1) from tab where id < a.id or (id = a.id and name = a.name)),id,name
from tab a
)tt
where tab.id = tt.id and tab.name = tt.name
id int,
name varchar(10)
)
insert into t
select 2,'ee'
union all select 2,'rr'
union all select 7,'xx'
union all select 9,'ss'
union all select 17,'ww'
--创建临时表
select identity(int,1,1) as [id],[name] into #t from t
--更新
update t set id=a.id from #t a where a.name=t.name
select * from t
--删除表
drop table #t
drop table t
问题解决