在很多的时候,我们会在数据库的表中设置一个字段: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那就表示数据不连续了。
类似文章有:
执行下面的语句生成测试表和测试记录
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
--
生成测试数据
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:测试表)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
--
拿到当前记录的下一个记录进行连接
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:错位记录)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
--
不连续的前前后后记录
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:效果)
-------------------华丽分割线-------------------
作者:
听风吹雨
版权:本文版权归作者和博客园共有
转载:欢迎转载,不过记得留下买路钱
Blog:
http://gaizai.i170.com/
格言:不喜欢是因为你不会 && 因为会所以喜欢