原文:
简单实用SQL脚本Part：查找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那就表示数据不连续了。
类似文章有：
执行下面的语句生成测试表和测试记录
--
生成测试数据
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 ]
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 ]
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
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：效果）
补充1：如果这个ID字段不是主键，那么就会有ID值重复的情况（有可能是一些误操作，之前就有遇到过）那么就需要top 1来处理。但是当前这种情况下可以使用下面的简化语句：（这些感谢忠毅）
select
a.id
as
oid, nid
=
( select min (id) from t_IDNotContinuous b where b.id > a.id)
from t_IDNotContinuous a
( select min (id) from t_IDNotContinuous b where b.id > a.id)
from t_IDNotContinuous a
补充2：缺失ID值列表，参考文献SQL顺序列找出断号（这些感谢高山老王）
--
方法一：找出上一条记录+1，再比较大小
select ( select max (id) + 1
from [ t_IDNotContinuous ]
where id < a.id) as beginId,
(id - 1 ) as endId
from [ t_IDNotContinuous ] a
where
a.id > ( select max (id) + 1 from [ t_IDNotContinuous ] where id < a.id)
select ( select max (id) + 1
from [ t_IDNotContinuous ]
where id < a.id) as beginId,
(id - 1 ) as endId
from [ t_IDNotContinuous ] a
where
a.id > ( select max (id) + 1 from [ t_IDNotContinuous ] where id < a.id)
（图4：效果）
--
方法二：全部+1，再判断在原来记录中找不到
select beginId,
( select min (id) - 1 from [ t_IDNotContinuous ] where id > beginId) as endId
from (
select id + 1 as beginId from [ t_IDNotContinuous ]
where id + 1 not in
( select id from [ t_IDNotContinuous ] )
and id < ( select max (id) from [ t_IDNotContinuous ] )
) as t
select beginId,
( select min (id) - 1 from [ t_IDNotContinuous ] where id > beginId) as endId
from (
select id + 1 as beginId from [ t_IDNotContinuous ]
where id + 1 not in
( select id from [ t_IDNotContinuous ] )
and id < ( select max (id) from [ t_IDNotContinuous ] )
) as t
参考文献：
自查询一例：求连续出现的次数（逻辑：找出相同值的某段范围，再统计个数）
