SQL标识列重新排序

最新推荐文章于 2021-02-27 03:30:22 发布

weixin_30387423

最新推荐文章于 2021-02-27 03:30:22 发布

阅读量139

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/gdjlc/archive/2010/07/27/2086891.html

select * from test

id          title
----------- ------------------------------------
1           20D32B5D-082C-47F1-9B84-FDD6F28700DC
3           7662E2C4-5F3D-4425-91D1-DBAEEC70C02B
5           F84E1617-D719-49DA-91F8-55DC76B66D2F
6           53B15573-FD6D-46E5-A32C-BE8041E475D4
8           7D42962D-365F-419A-B026-4A62440A3B43
9           1A44340F-B78C-446E-A4D6-661EBA663EF3

(6 行受影响)

--先取消标识列id，再执行下面代码，执行后再设置id为标识列
--下面代码中关键地方：
--1、先找出总记录数作为循环次数
--2、每次循环中要找出当前id
declare @index int
set @index = 1
declare @count int
set @count = (select count(*) from test)
while(@index < @count+1)
begin
    exec('update test set id=' + @index + ' where id = (select max(id) from (select top '+ @index + ' id from test) t)')
    set @index = @index + 1
end

select * from test

id          title
----------- ------------------------------------
1           20D32B5D-082C-47F1-9B84-FDD6F28700DC
2           7662E2C4-5F3D-4425-91D1-DBAEEC70C02B
3           F84E1617-D719-49DA-91F8-55DC76B66D2F
4           53B15573-FD6D-46E5-A32C-BE8041E475D4
5           7D42962D-365F-419A-B026-4A62440A3B43
6           1A44340F-B78C-446E-A4D6-661EBA663EF3

(6 行受影响)