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请问大家这语句有没有性能问题,,或者有没有更好的方法
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请问大家这语句有没有性能问题,,或者有没有更好的方法
select *,row_id=row_number() over(order by id) from table1
)update cte
set id=row_id
set id = (select count(1) from tb where id < t.id) + 1
from tb t
insert into tb values(7 ,'1')
insert into tb values(3 ,'2')
insert into tb values(2 ,'3')
insert into tb values(6 ,'4')
goupdate tb
set id = (select count(1) from tb where id < t.id) + 1
from tb tselect * from tbdrop table tb/*
id name
----------- ----------
4 1
2 2
1 3
3 4(所影响的行数为 4 行)*/
@i=@i+1=1,id=@i
--第二条
@i=@i+1=2,,id=@i
。。
都先执行 @i=@i+11L的方法可以 适用于2005
如果是2000 就用楼主你的方法吧
dawugui 的方法还没有理解
create table tb(id int,name varchar(10))
insert into tb values(7 ,'1')--4
insert into tb values(3 ,'2')--2
insert into tb values(2 ,'3')--相对位置为1
insert into tb values(6 ,'4')--3
update a
set id=number
from(select *,row_number() over(order by id) number from tb) aselect * from tb
id name
----------- ----------
4 1
2 2
1 3
3 4(4 行受影响)
name score
aa 99
bb 56
cc 56
dd 77
ee 78
ff 76
gg 78
ff 501. 名次生成方式1,Score重复时合并名次
SELECT * , Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score)
FROM jh03 a
ORDER BY Place
结果
Name Score Place
---------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 3
ff 76.00 4
bb 56.00 5
cc 56.00 5
ff 50.00 62. 名次生成方式2 , Score重复时保留名次空缺
SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1
FROM jh03 a
ORDER BY Place
结果
Name Score Place
--------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 4
ff 76.00 5
bb 56.00 6
cc 56.00 6
ff 50.00 8
但不知道性能跟我的 那个好些,但语句绝对是简洁很多,,DECLARE @i int
SET @i = 0
UPDATE table1 SET @i = @i + 1, id = @i
WHERE id IN (SELECT TOP 100 PERCENT id FROM table1 ORDER BY id)
--trydeclare @t table
(id int,
name int)
insert into @t select 7,1
union all select 3,2
union all select 2,3
union all select 6,4
select id= (select count(1) from @t where name>=a.name),
name from @t a
/*id name
----------- -----------
4 1
3 2
2 3
1 4(4 行受影响)*/