SQL 自增列有关问题

2013-06-25

SQL自增列问题环境：数据量很多，因为有删除数据，出现不连续的自增ID需求：从第十行开始，也就是说从ID10开始

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

SQL 自增列有关问题