declare @t table([id] int,[col] varchar(1)) insert @t select 1,'a' union all select 2,'b' union all select 3,'c' union all select 4,'d' union all select 5,'e' union all select 6,'f' union all select 7,'g' union all select 8,'h'update @t set id = id - 1 where id > 3 select * from @t /* id col ----------- ---- 1 a 2 b 3 c 3 d 4 e 5 f 6 g 7 h */ 不清楚楼主要问什么....
就是比如我有一下几列 ID A B 1 w r 2 s d 3 d e 5 w r 6 r w 我想把这个表改成如下这样: ID A B 1 w r 2 s d 3 d e 4 w r 5 r w也就是说ID要连续排列
create table yu (ID int, A varchar(2), B varchar(2))insert into yu select 1, 'w', 'r' union all select 2, 's', 'd' union all select 3, 'd', 'e' union all select 5, 'w', 'r' union all select 6, 'r', 'w' select identity(int,1,1) NID,A,B into #yu from yutruncate table yuinsert into yu(ID,A,B) select NID,A,B from #yudrop table #yuselect * from yu/* ID A B ----------- ---- ---- 1 w r 2 s d 3 d e 4 w r 5 r w(5 row(s) affected) */
declare @T table([ID] int,[A] varchar(1),[B] varchar(1)) insert @T select 1,'w','r' union all select 2,'s','d' union all select 3,'d','e' union all select 5,'w','r' union all select 6,'r','w' --假设上面的是你的表 select row_number() over (order by ID) as ID,[A],[B] from @T /* ID A B -------------------- ---- ---- 1 w r 2 s d 3 d e 4 w r 5 r w */
数据量不大的话,先备份数据库,然后运行4#的下面部分代码就可以了select identity(int,1,1) NID,A,B into #yu from yutruncate table yuinsert into yu(ID,A,B) select NID,A,B from #yudrop table #yu 4#的方法是,先取出原数据,用identity(int,1,1)生成连续ID,存入表变量#yu 清空原表 再把 #yu的内容插入原表,
declare @id int select @id=0 update aa_aaa set ID=0 --重置 update aa_aaa set @id=@id+1,ID=ID+@id --顺序增长ID 关键在于 @id=@id+1自增或者 将ID 等同于行号 update aa_aaa set ID=(select count(ID) from aa_aaa as b where b.ID<=aa_aaa.ID)
declare @t table([id] int,[col] varchar(1))
insert @t
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 5,'e' union all
select 6,'f' union all
select 7,'g' union all
select 8,'h'update @t set id = id - 1 where id > 3
select * from @t
/*
id col
----------- ----
1 a
2 b
3 c
3 d
4 e
5 f
6 g
7 h
*/
不清楚楼主要问什么....
ID A B
1 w r
2 s d
3 d e
5 w r
6 r w
我想把这个表改成如下这样:
ID A B
1 w r
2 s d
3 d e
4 w r
5 r w也就是说ID要连续排列
create table yu
(ID int, A varchar(2), B varchar(2))insert into yu
select 1, 'w', 'r' union all
select 2, 's', 'd' union all
select 3, 'd', 'e' union all
select 5, 'w', 'r' union all
select 6, 'r', 'w'
select identity(int,1,1) NID,A,B
into #yu
from yutruncate table yuinsert into yu(ID,A,B)
select NID,A,B from #yudrop table #yuselect * from yu/*
ID A B
----------- ---- ----
1 w r
2 s d
3 d e
4 w r
5 r w(5 row(s) affected)
*/
表我已经准备好了,只需要一个SQL语句执行一下就可以,
我觉得这个语句应该不复杂
declare @T table([ID] int,[A] varchar(1),[B] varchar(1))
insert @T
select 1,'w','r' union all
select 2,'s','d' union all
select 3,'d','e' union all
select 5,'w','r' union all
select 6,'r','w'
--假设上面的是你的表
select row_number() over (order by ID) as ID,[A],[B] from @T
/*
ID A B
-------------------- ---- ----
1 w r
2 s d
3 d e
4 w r
5 r w
*/
清空原表,
再把 #yu中的数据insert到原表,
into #yu
from yutruncate table yuinsert into yu(ID,A,B)
select NID,A,B from #yudrop table #yu
4#的方法是,先取出原数据,用identity(int,1,1)生成连续ID,存入表变量#yu
清空原表
再把 #yu的内容插入原表,
declare @id int
select @id=0
update aa_aaa set ID=0 --重置
update aa_aaa set @id=@id+1,ID=ID+@id --顺序增长ID 关键在于 @id=@id+1自增或者 将ID 等同于行号
update aa_aaa set ID=(select count(ID) from aa_aaa as b where b.ID<=aa_aaa.ID)