如何查找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 ]

   

（图 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：效果）

 

http://www.jzxue.com/shujuku/mssqlserver/201008/30-4483.html

转载于:https://www.cnblogs.com/eva_2010/articles/2680122.html