简单实用SQL脚本Part:查找SQL Server 自增ID值不连续记录-阿里云开发者社区

开发者社区> 杰克.陈> 正文

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

简介: 原文:简单实用SQL脚本Part:查找SQL Server 自增ID值不连续记录        在很多的时候,我们会在数据库的表中设置一个字段:ID,这个ID是一个IDENTITY,也就是说这是一个自增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 (11)  NOT NULL,
[ValuesString] [nchar]  (10NULL)

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 - 1as 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
-1as 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顺序列找出断号

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【转】Oracle 查看表空间使用率 SQL 脚本
文章转自:http://blog.csdn.net/tianlesoftware/article/details/7619732 1 /* Formatted on 2012/5/31 14:51:13 (QP5 v5.
658 0
用字符串连接SQL语句并用EXEC执行时,出现名称 '‘不是有效的标识符
原文:用字符串连接SQL语句并用EXEC执行时,出现名称 '‘不是有效的标识符  用字符串连接SQL语句并用EXEC执行时,出现名称 '这里是字符串连接的一条SQL语句‘不是有效的标识符  才发现,在写exec @sql 时,忘了在@sql加(),这样写 exec (@sql) 就不会出错了!
593 0
【转】SQL删除重复记录,只保留其中一条
SQL:删除重复数据,只保留一条用SQL语句,删除掉重复项只保留一条在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢  1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断 select * from people where peopleId...
615 0
记录一下,Entity SQL
相关网址: http://msdn.microsoft.com/zh-cn/library/bb387145.aspx
441 0
SQL Server 查找全部重复记录
Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)&gt;1)
768 0
简单实用SQL脚本Part:查找SQL Server 自增ID值不连续记录
原文:简单实用SQL脚本Part:查找SQL Server 自增ID值不连续记录        在很多的时候,我们会在数据库的表中设置一个字段:ID,这个ID是一个IDENTITY,也就是说这是一个自增ID。
1217 0
+关注
杰克.陈
一个安静的程序猿~
10427
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载