查找SQL Server自增ID不连续记录

解决办法的核心思想：获取到当前记录的下一条记录的ID值，再判断这两个ID值是否差值为1，如果不为1那就表示数据不连续了。

 

-------------------------生成测试数据--------------------------
if exists (select * from sysobjects where id = OBJECT_ID('[hr_z_zfuser]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [hr_z_zfuser]

CREATE TABLE [hr_z_zfuser] (
[ID] [int]  IDENTITY (1, 1)  NOT NULL,
[sfzh] [nchar]  (10) NULL)

SET IDENTITY_INSERT [hr_z_zfuser] ON

INSERT [hr_z_zfuser] ([ID],[sfzh]) VALUES ( 1,'身份证号１')
INSERT [hr_z_zfuser] ([ID],[sfzh]) VALUES ( 2,'身份证号２')
INSERT [hr_z_zfuser] ([ID],[sfzh]) VALUES ( 3,'身份证号３')
INSERT [hr_z_zfuser] ([ID],[sfzh]) VALUES ( 6,'身份证号５')
INSERT [hr_z_zfuser] ([ID],[sfzh]) VALUES ( 7,'身份证号６')　
INSERT [hr_z_zfuser] ([ID],[sfzh]) VALUES ( 9,'身份证号４')

SET IDENTITY_INSERT [hr_z_zfuser] OFF

select * from [hr_z_zfuser]

 

 

-------------------拿到当前记录的下一个记录进行连接----------------------
select ID,new_ID--
into [hr_z_zfuser_temp]
from (
select ID,new_ID = (
select top 1 ID from [hr_z_zfuser]
where ID=(select min(ID) from [hr_z_zfuser] where ID>a.ID)
)
from [hr_z_zfuser] as a
) as b

select * from [hr_z_zfuser_temp]

 

 

--------------------得到不连续ＩＤ-----------------------------
select * from hr_z_zfuser_temp where ID <> new_ID - 1

 

 

--不连续的前前后后记录
select a.* from hr_z_zfuser a inner join (select * from hr_z_zfuser_temp where ID <> new_ID - 1)  b
on a.ID >= b.ID and a.ID <=b.new_ID
order by a.ID