sql添加序号方法
[sql]
create table test
( www.2cto.com
id int identity (1,1),
orderno int,
name varchar(10)
)
truncate table test
insert into test(name) values ('张三1')
insert into test(name) values ('张三2')
insert into test(name) values ('张三3')
insert into test(name) values ('张三4')
insert into test(name) values ('张三5')
insert into test(name) values ('张三1')
insert into test(name) values ('张三2')
insert into test(name) values ('张三3')
insert into test(name) values ('张三4')
insert into test(name) values ('张三5')
delete from test where id in (3,5,6)
www.2cto.com
1 NULL 张三1
2 NULL 张三2
4 NULL 张三4
7 NULL 张三2
8 NULL 张三3
9 NULL 张三4
10 NULL 张三5
[sql]
update test set orderno =(
select aa from ( select ROW_NUMBER() over (order by id ) as aa ,id from test c ) b
where test.id= b.id)
www.2cto.com
1 1 张三1
2 2 张三2
4 3 张三4
7 4 张三2
8 5 张三3
9 6 张三4
10 7 张三5
如果
数据库不支持row_number 则创建临时表(自增id, test.id) 然后Update
另一个方法 这个自己看着做
select IDENTITY(int,1,1)as xh , name into #c from test
作者 liyangfd