SQL Server自增ID值不连续记录的实现方法

tznktg

在很多的时候，我们会在数据库的表中设置一个字段：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：记录往上回填信息

执行下面的语句生成测试表和测试记录

–生成测试数据

1.if exists (select * from sysobjects 2.where id = OBJECT_ID(’[t_IDNotContinuous]‘) 3.and OBJECTPROPERTY(id, ‘IsUserTable’) = 1) 4.DROP TABLE [t_IDNotContinuous] 5. 6.CREATE TABLE [t_IDNotContinuous] ( 7.[ID] [int] IDENTITY (1, 1) NOT NULL, 8.[ValuesString] [nchar] (10) NULL) 9. 10.SET IDENTITY_INSERT [t_IDNotContinuous] ON 11. 12.INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 1,’test’) 13.INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 2,’test’) 14.INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 3,’test’) 15.INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 5,’test’) 16.INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 6,’test’) 17.INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 7,’test’) 18.INSERT [t_IDNotContinuous] ([ID],[ValuesString]) VALUES ( 10,’test’) 19. 20.SET IDENTITY_INSERT [t_IDNotContinuous] OFF 21. 22.select * from [t_IDNotContinuous]
1.select ID,new_ID 2.into [t_IDNotContinuous_temp] 3.from ( 4.select ID,new_ID = ( 5.select top 1 ID from [t_IDNotContinuous] 6.where ID=(select min(ID) from [t_IDNotContinuous] where ID>a.ID) 7.) 8.from [t_IDNotContinuous] as a 9.) as b 10. 11.select * from [t_IDNotContinuous_temp]
不连续的前前后后记录

1.select * 2.from [t_IDNotContinuous_temp] 3.where ID new_ID - 1
–查询原始记录

1.select a.* from [t_IDNotContinuous] as a 2.inner join (select * 3.from [t_IDNotContinuous_temp] 4.where ID new_ID - 1) as b 5.on a.ID >= b.ID and a.ID <=b.new_ID 6.order by a.ID