sql 将 rownumber 替换到 Id

存在一张测试表

if exists(select * from sys.tables where name ='test')

drop table test

CREATE table test(

 id int,

 name varchar(50),

 address varchar(50))

 insert into test values(2,'aa','address1')

 insert into test values(3,'bb','address2')

 insert into test values(5,'cc','address3')

 insert into test values(8,'dd','address4')

 insert into test values(11,'ee','address5')

此表中Id 是不连续的

;with cte(rownum,id) as(

    select ROW_NUMBER()over(order by id),id  from test 

)

update test set id= rownum  from cte  as t1   where  test.id = t1.id 

将行号替换成原本的Id
现在的Id 连续起来了

积累一个小技巧，日后总会用到