解决办法的核心思想:获取到当前记录的下一条记录的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,'身份证号1')
INSERT [hr_z_zfuser] ([ID],[sfzh]) VALUES ( 2,'身份证号2')
INSERT [hr_z_zfuser] ([ID],[sfzh]) VALUES ( 3,'身份证号3')
INSERT [hr_z_zfuser] ([ID],[sfzh]) VALUES ( 6,'身份证号5')
INSERT [hr_z_zfuser] ([ID],[sfzh]) VALUES ( 7,'身份证号6')
INSERT [hr_z_zfuser] ([ID],[sfzh]) VALUES ( 9,'身份证号4')
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]
--------------------得到不连续ID-----------------------------
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