ÇëÎÊÒ»ÌõSQLÓï¾äµÄÐÔÄÜÎÊÌâ - MS-SQL Server / »ù´¡Àà

SQL code:


DECLARE @i int
SET @i = 0
UPDATE table1 SET @i = @i + 1, id = @i WHERE id > @i AND id IN (SELECT TOP 100 PERCENT id from table1 ORDER BY id)




¸ÃÓï¾äʵÏÖ ÖØÐÂÐòÁл¯idʹid³ÉΪÖØ1¿ªÊ¼µÄÁ¬Ðø¼Ç¼, ¶ø²»¸Ä±ä¼Ç¼µÄÏà¶ÔidµÄÅÅÐòλÖÃ,ʹ
id name
7 1
3 2
2 3
6 4

ÐòÁл¯ºó
id name
4 1
2 2
1 3
3 4

ÇëÎÊ´ó¼ÒÕâÓï¾äÓÐûÓÐÐÔÄÜÎÊÌâ,,»òÕßÓÐûÓиüºÃµÄ·½·¨

---

SQL code:
with cte as(
select *,row_id=row_number() over(order by id) from table1
)

update cte
set id=row_id

---

update tb
set id = (select count(1) from tb where id < t.id) + 1 
from tb t

---

SQL code:
create table tb(id int,name varchar(10))
insert into tb values(7 ,'1')
insert into tb values(3 ,'2')
insert into tb values(2 ,'3')
insert into tb values(6 ,'4')
go

update tb
set id = (select count(1) from tb where id < t.id) + 1
from tb t

select * from tb

drop table tb

/*
id name
----------- ----------
4 1
2 2
1 3
3 4

£¨ËùÓ°ÏìµÄÐÐÊýΪ 4 ÐУ©

*/

{