尋找SQL Server 自增ID值不連續記錄

標籤：

在很多的時候，我們會在資料庫的表中設定一個欄位：ID，這個ID是一個IDENTITY，也就是說這是一個自增ID。當並發量很大並且這個欄位不是主鍵的時候，就有可能會讓這個值重複；或者在某些情況（例如插入資料的時候出錯，或者是使用者使用了Delete刪除了記錄）下會讓ID值不是連續的，比如1,2,3,5,6,7,10，那麼在中間就斷了幾個資料，那麼我們希望能在資料中找出這些相關的記錄，我希望找出的記錄是3,5,7,10，通過這些記錄可以查看這些記錄的規律來分析或者統計；又或者我需要知道那些ID值是沒有的：4,8,9。

解決辦法的核心思想是： 擷取到目前記錄的下一條記錄的ID值，再判斷這兩個ID值是否差值為1，如果不為1那就表示資料不連續了

執行下面的語句產生測試表和測試記錄

 1 --產生測試資料 2 if exists (select * from sysobjects where id = OBJECT_ID(‘[t_IDNotContinuous]‘) and OBJECTPROPERTY(id, ‘IsUserTable‘) = 1)  3 DROP TABLE [t_IDNotContinuous] 4  5 CREATE TABLE [t_IDNotContinuous] ( 6 [ID] [int]  IDENTITY (1, 1)  NOT NULL, 7 [ValuesString] [nchar]  (10) NULL) 8  9 SET IDENTITY_INSERT [t_IDNotContinuous] ON10 11 INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 1,‘test‘)12 INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 2,‘test‘)13 INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 3,‘test‘)14 INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 5,‘test‘)15 INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 6,‘test‘)16 INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 7,‘test‘)17 INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 10,‘test‘)18 19 SET IDENTITY_INSERT [t_IDNotContinuous] OFF20 21 select * from [t_IDNotContinuous]

（圖1：測試表）

 1 --拿到目前記錄的下一個記錄進行串連 2 select ID,new_ID 3 into [t_IDNotContinuous_temp] 4 from ( 5 select ID,new_ID = ( 6 select top 1 ID from [t_IDNotContinuous] 7 where ID=(select min(ID) from [t_IDNotContinuous] where ID>a.ID) 8 ) 9 from [t_IDNotContinuous] as a10 ) as b11 12 select * from [t_IDNotContinuous_temp]

(圖2：錯位記錄)

 1 --不連續的前前後後記錄 2 select *  3 from [t_IDNotContinuous_temp] 4 where ID <> new_ID - 1 5  6  7 --查詢原始記錄 8 select a.* from [t_IDNotContinuous] as a 9 inner join (select * 10 from [t_IDNotContinuous_temp]11 where ID <> new_ID - 1) as b12 on a.ID >= b.ID and a.ID <=b.new_ID13 order by a.ID

（圖3：效果）

 

補充1：如果這個ID欄位不是主鍵，那麼就會有ID值重複的情況（有可能是一些誤操作，之前就有遇到過）那麼就需要top 1來處理。但是當前這種情況下可以使用下面的簡化語句

1 select a.id as oid, nid = 2 (select min(id) from t_IDNotContinuous b where b.id > a.id) 3 from t_IDNotContinuous a

補充2：缺失ID值列表，

1--方法一：找出上一條記錄+1，再比較大小2 select (select max(id)+1 3 from [t_IDNotContinuous] 4 where id<a.id) as beginId,5 (id-1) as endId6 from [t_IDNotContinuous] a7 where8 a.id>(select max(id)+1 from [t_IDNotContinuous] where id<a.id)

 

（圖4：效果）

1 --方法二：全部+1，再判斷在原來記錄中找不到2 select beginId,3 (select min(id)-1 from [t_IDNotContinuous] where id > beginId) as endId 4 from (  5 select id+1 as beginId from [t_IDNotContinuous]6 where id+1 not in 7 (select id from [t_IDNotContinuous]) 8 and id < (select max(id) from [t_IDNotContinuous])  9 ) as t

 

尋找SQL Server 自增ID值不連續記錄