读书人
栏目▼

SQL 自增列有关问题

发布时间： 2013-06-25 23:45:41 作者： rapoo

SQL 自增列问题
环境：数据量很多，因为有删除数据，出现不连续的自增ID

需求：从第十行开始，
也就是说从ID=10开始，
让10后面的ID接上来，
也就是说后面的ID是11，12，13.....N

求代码实现


[解决办法]
一个案例,供参考.


create table lee
( id int identity(1,1) not null,
  name varchar(10)
  constraint pk_lee primary key(id)  
)
 
insert into lee(name)
 select 'aa' union all
 select 'bb' union all
 select 'cc' union all
 select 'dd' union all
 select 'ee'
 
select id,name from lee
 
/*
id          name
----------- ----------
1           aa
2           bb
3           cc
4           dd
5           ee
 
(5 row(s) affected)
*/
 
--原先在表中删除了的一条数据
delete from lee where id=3
  
--现在要重新添加回那张表中
insert into lee(name) select 'cc'
 
--可是主键不是连续的了
select id,name from lee
 
/*
id          name
----------- ----------
1           aa
2           bb
4           dd
5           ee
6           cc
 
(5 row(s) affected)
*/
 
--主键跟删除之前那样使得主键ID连续
delete from lee where id=6  --> 删除测试记录
 
set identity_insert lee on
 
insert into lee(id,name) select 3,'cc'
 
set identity_insert lee off
 
 
select id,name from lee
 
/*
id          name
----------- ----------
1           aa
2           bb
3           cc
4           dd
5           ee
 
(5 row(s) affected)
*/

[解决办法]
为什么会有这种需求，强迫症吗？
[解决办法]
id如果与外表已经关联了，这样直接改id，关联不就（几乎）全部错了？



id过大，可以采用bigint（64位，最多4GG）
[解决办法]
alter table tb drop id
alter table add id int identity(1,1)


http://blog.csdn.net/roy_88/article/details/1424370
[解决办法]
SQL 自增列有关问题如果表有外键 建议你放弃这样做 Id不连续就不连续吧！
SQL 自增列有关问题受过这种苦的深有体会 其实无所谓的 第一次发现的时候觉得不好 所以总想他连续
[解决办法]
给你贴下 去看书去了
if object_id('T') is not null

    drop table T

go

create table T(id  INT IDENTITY,name nvarchar(10))

 

go

 

 

CREATE  TRIGGER Tr_dT ON T

INSTEAD OF DELETE

AS

SET NOCOUNT ON ;

BEGIN

 

 

    DECLARE @MinID INT

    SELECT @MinID=MIN(ID)-1 FROM DELETED

    DELETE T WHERE ID IN(SELECT ID FROM deleted)   

   

    SELECT ID=ID*1,NAME INTO # FROM T   WHERE ID>@MinID

    DELETE T WHERE ID>@MinID

    UPDATE # SET ID=@MinID,@MinID=@MinID+1

    SET IDENTITY_INSERT TON ;

    INSERT INTO T(ID,Name)SELECT ID,Name FROM #

    SET IDENTITY_INSERT TOFF;

    DBCC CHECKIDENT('T',RESEED,@MinID)

    DROP TABLE #

   

END

go

--新增

insert T(Name) values('AA')

insert T(Name) values('BB')

insert T(Name) values('CC')

insert T(Name) values('DD')

insert T(Name) values('EE')

GO

SELECT * FROMT

/*

id  name

1       AA

2       BB

3       CC

4       DD

5       EE

*/

 

go

--除

DELETE T WHERE ID IN(2,3)

go

insert T(Name) values('FF')

 

SELECT * FROMT

/*

id  name

1   AA

2   DD

3   EE

4   FF

*/

go

drop table T

[解决办法]
引用:
Quote: 引用:

如果只是需要显示一个连续的序号，可以使用row_number()在查询时生成


现在数据已经存在了 只能更改ID 而不能删除数据


row_number()不会删除数据啊，只是查询的时候多出一列序号 而已
[解决办法]
你是要修改已经生成的ID列 比如
1 2 3 4 5 6 7 8 9 10 15 16 17
改成
1 2 3 4 5 6 7 8 9 10 11 12 13
还是说
现在是
1 2 3 4 5 6 7 8 9 10 但是如果新增ID列就会变成 15 接着 16 17 18
想变成
1 2 3 4 5 6 7 8 9 10 11 12 13
[解决办法]

create table xm(ID int)

insert into xm(ID)
 select 1 union all
 select 2 union all
 select 7 union all
 select 10 union all
 select 11 union all
 select 12 union all
 select 18 union all
 select 19 union all
 select 20 union all
 select 21


update a
 set a.ID=b.rn
 from xm a
 inner join (select ID,row_number() over(order by ID)+9 'rn' 
             from xm where ID>=10) b on a.ID=b.ID
 where a.ID>=10

-- 结果
select ID from xm

/*
ID
-----------
1
2
7
10
11
12
13
14
15
16

(10 row(s) affected)
*/

[解决办法]
1、先alter table，去掉列的自增。
2、update table，将这个列清空。
3、alter table，再给这个列增加自增。

这样就可以了。

但如果表太大，而且大家都在用，呵呵………………
[解决办法]
引用:
环境：数据量很多，因为有删除数据，出现不连续的自增ID

需求：从第十行开始，
也就是说从ID=10开始，
让10后面的ID接上来，
也就是说后面的ID是11，12，13.....N

求代码实现

DBCC CHECKIDENT('表名',RESEED,10)
[解决办法]
你把数据除ID列导出到一个临时表，然后truncate源表，再插回去，ID就连续了
[解决办法]
你要啥方法？有什么限制？对于大数据量，我的方法已经很好了哦
[解决办法]

create table xm(ID int not null identity(1,1))
 
set identity_insert xm on

insert into xm(ID)
 select 1 union all
 select 2 union all
 select 7 union all
 select 10 union all
 select 11 union all
 select 12 union all
 select 18 union all
 select 19 union all 
 
 select 20 union all
 select 21
 
set identity_insert xm off


select ID,
       case when ID<10 then ID
       else row_number() over(order by ID)+9-(select count(1) from xm where ID<10) end 'NewID'
into #t
from xm
  
truncate table xm

set identity_insert xm on

insert into xm(ID)
 select [NewID] from #t

set identity_insert xm off

-- 结果
select ID from xm
 
/*
ID
-----------
1
2
7
10
11
12
13
14
15
16

(10 row(s) affected)
*/

[解决办法]
这段代码可以解释详细点吗？
)+9是啥意思？
--> 因为是从ID>=10开始重新编号,
新编号 = 原表排序编号 + 9 - (ID小于10的ID个数)
LZ可手工计算一下,都符合以上公式的规律的.

有点类似数学中数组的通项公式,要发现和总结数据中潜在的规律.
[解决办法]

select ID,
       row_number() over(order by ID) '原表排序编号' 
from xm

/*
ID          原表排序编号
----------- --------------------
1           1
2           2
7           3
10          4
11          5
12          6
18          7
19          8
20          9
21          10

(10 row(s) affected)
*/

[解决办法]
写入临时表时,其他字段照抄..

select ID,
       case when ID<10 then ID
       else row_number() over(order by ID)+9-(select count(1) from xm where ID<10) end 'NewID',
       [字段列表]
into #t
from xm


插回原表时,其他字段也照抄..

insert into xm(ID,[字段列表])
 select [NewID],[字段列表] from #t

读书人网 >SQL Server

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net