文章摘要
这篇文章介绍了一种使用SQL生成连续SNId（序列号）的方法，并通过检查SNId序列的完整性来检测潜在的间隙或缺失。具体步骤如下：
1. **生成SNId序列**：在`tempdb`中创建了一个名为`t_MaxInMinContinuousArr`的表，并使用自增计数器`@i`填充`SNId`和`@tRes`字段。
2. **随机删除SNId**：通过`CHECKSUM`函数随机删除部分`SNId`值。
3. **检测缺失SNId**：使用CTE（常见表表达式）结合`WITH`语句，首先定义了`TMinAndMaxSNId`来获取序列的最小和最大值，然后定义`TContinuousId`来获取预期的连续SNId范围。通过`EXCEPT`操作，找出实际存在的`SNId`与预期范围之间的缺失值。
这种方法虽然非高效，但能够确保SNId序列的完整性，适用于需要可靠数据完整性场景的非专业SQL实现。
————————————————————————————-
–by wls
–非专业SQL 不求高效 但求能跑
USE tempdb
GO
————————————————————————————-
IF OBJECT_ID (N’t_MaxInMinContinuousArr’, N’U’) IS NOT NULL
DROP TABLE t_MaxInMinContinuousArr;
GO
CREATE TABLE t_MaxInMinContinuousArr(SNId INTEGER PRIMARY KEY,SomeDate DATETIME)
GO
————————————————————————————-
DECLARE @i INT
SET @i=–SNId起始值
DECLARE @TestScale INTEGER
SET @TestScale=+@i –数据规模
DECLARE @t DATETIME ,
@t DATETIME ,
@dd INT ,
@dayadd INT ,
@tRes DATETIME
SET @t=’– ::’
SET @t=’– ::’
SET @dd=DATEDIFF(dd, @t, @t)
WHILE @i < @TestScale –数据规模
BEGIN
SET @dayadd=@dd * RAND()
SET @tRes=DATEADD(dd, @dayadd, @t) + RAND()
INSERT INTO t_MaxInMinContinuousArr VALUES(@i , @tRes)
SET @i=@i +
END
GO
–SELECT TOP * FROM t_MaxInMinContinuousArr
–GO
————————————————————————————-
–Delete some SNId randomly
DECLARE @TestScale INTEGER
SET @TestScale=–数据规模
DELETE FROM t_MaxInMinContinuousArr WHERE SNId=–(SELECT abs(checksum(newid()))%@TestScale + )
DELETE FROM t_MaxInMinContinuousArr WHERE SNId=–(SELECT abs(checksum(newid()))%@TestScale + )
GO
–SELECT TOP * FROM t_MaxInMinContinuousArr
–GO
————————————————————————————-
–now find the SNId that SNId+ is missing.
WITH TMinAndMaxSNId
AS(
SELECT MIN(SNId) AS MinSNId,MAX(SNId) AS MaxSNId FROM t_MaxInMinContinuousArr –The min and max SNId
),
TContinuousId
AS
(
SELECT number AS SNIdCmped FROM master..spt_values,TMinAndMaxSNId WHERE type=’p’ AND number >=TMinAndMaxSNId.MinSNId AND number <=TMinAndMaxSNId.MaxSNId
)
SELECT MIN(res.SNIdCmped)- FROM
(
SELECT SNIdCmped FROM TContinuousId
EXCEPT
SELECT SNId FROM t_MaxInMinContinuousArr) AS res
GO