SQL Server 2008 R2——查找最小nIndex,nIndex存在而nIndex+1不存在求最小连续数组中的最大值（mysql查询最小值并显示名称）太疯狂了

🌐 经济型：买域名、轻量云服务器、用途：游戏网站等《腾讯云》特点：特价机便宜适合初学者用点我优惠购买

🚀 拓展型：买域名、轻量云服务器、用途：游戏网站等《阿里云》特点：中档服务器便宜域名备案事多点我优惠购买

🛡️ 稳定型：买域名、轻量云服务器、用途：游戏网站等《西部数码》特点：比上两家略贵但是稳定性超好事也少点我优惠购买

文章摘要

这篇文章介绍了一种使用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