with cte as ( select rn=row_number()over(order by id),* from tb ) update cte set id=rn;
--> 测试数据: [test] if object_id('[test]') is not null drop table [test] create table [test] (ID int,name varchar(4)) insert into [test] select 1,'阿勒' union all select 2,'悬空' union all select 3,'酥软' union all select 5,'andy' union all select 6,'bean'declare @i int set @i=0 update test set id=@i,@i=@i+1--测试 select * from [test]--结果: ID name ----------- ---- 1 阿勒 2 悬空 3 酥软 4 andy 5 bean
if object_id('test') is not null drop table testcreate table test([id] int,[name] varchar(4)) insert test select 1,'阿勒' union all select 2,'悬空' union all select 3,'酥软' union all select 5,'andy' union all select 6,'bean'with result_test as ( select rn=row_number()over(order by id),* from test ) update result_test set id=rn;select * from test
(
select rn=row_number()over(order by id),*
from tb
)
update cte
set id=rn;
--> 测试数据: [test]
if object_id('[test]') is not null drop table [test]
create table [test] (ID int,name varchar(4))
insert into [test]
select 1,'阿勒' union all
select 2,'悬空' union all
select 3,'酥软' union all
select 5,'andy' union all
select 6,'bean'declare @i int
set @i=0
update test set id=@i,@i=@i+1--测试
select * from [test]--结果:
ID name
----------- ----
1 阿勒
2 悬空
3 酥软
4 andy
5 bean
with cte as 这句话的意思是做什么的?
你可以理解为一个临时表
drop table testcreate table test([id] int,[name] varchar(4))
insert test
select 1,'阿勒' union all
select 2,'悬空' union all
select 3,'酥软' union all
select 5,'andy' union all
select 6,'bean'with result_test as
(
select rn=row_number()over(order by id),*
from test
)
update result_test
set id=rn;select * from test