简单实用SQL脚本Part：查找SQL Server 自增ID值不连续记录

原文: 简单实用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那就表示数据不连续了。

类似文章有：

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

 

补充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

 

补充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)

 

（图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

 

参考文献：

自查询一例：求连续出现的次数（逻辑：找出相同值的某段范围，再统计个数）

SQL顺序列找出断号