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. 簡單但有用的SQL指令碼Part6：特殊需要的行轉列

2. 簡單但有用的SQL指令碼Part9：記錄往上回填資訊

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

--產生測試資料

 

  
if exists (select * from sysobjects 
  
where id = OBJECT_ID('[t_IDNotContinuous]') 
  
and OBJECTPROPERTY(id, 'IsUserTable') = 1)   
  
DROP TABLE [t_IDNotContinuous]  
  
 
  
CREATE TABLE [t_IDNotContinuous] (  
  
[ID] [int]  IDENTITY (1, 1)  NOT NULL,  
  
[ValuesString] [nchar]  (10) NULL)  
  
 
  
SET IDENTITY_INSERT [t_IDNotContinuous] ON 
  
 
  
INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 1,'test')  
  
INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 2,'test')  
  
INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 3,'test')  
  
INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 5,'test')  
  
INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 6,'test')  
  
INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 7,'test')  
  
INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 10,'test')  
  
 
  
SET IDENTITY_INSERT [t_IDNotContinuous] OFF 
  
 
  
select * from [t_IDNotContinuous] 
 

 

圖1：測試表） 

--拿到目前記錄的下一個記錄進行串連

 

  
select ID,new_ID  
  
into [t_IDNotContinuous_temp]  
  
from (  
  
select ID,new_ID = (  
  
select top 1 ID from [t_IDNotContinuous]  
  
where ID=(select min(ID) from [t_IDNotContinuous] where ID>a.ID)  
  
)  
  
from [t_IDNotContinuous] as a  
  
) as b  
  
 
  
select * from [t_IDNotContinuous_temp] 
 

圖2：錯位記錄） 

--不連續的前前後後記錄

 

  
select *   
  
from [t_IDNotContinuous_temp]  
  
where ID <> new_ID - 1 
 

--查詢原始記錄

 

  
select a.* from [t_IDNotContinuous] as a  
  
inner join (select *   
  
from [t_IDNotContinuous_temp]  
  
where ID <> new_ID - 1) as b  
  
on a.ID >= b.ID and a.ID <=b.new_ID  
  
order by a.ID 
 

圖3：效果） 

原文標題：尋找SQL Server 自增ID值不連續記錄

連結：http://www.cnblogs.com/gaizai/archive/2010/08/30/1812717.html