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

文章摘要

这篇文章描述了一段SQL代码，用于生成一个时间序列数据表，并进行数据验证。代码的主要步骤包括： 1. **表的创建**：创建了一个名为`t_MaxInMinContinuousArr`的表，字段为`SNId`（主键）和`SomeDate`（日期）。 2. **数据生成**：通过循环和随机函数生成数据，`@TestScale`表示数据规模，`@tRes`表示随机生成的日期。 3. **数据删除**：随机删除了部分`SNId`值。 4. **数据验证**：通过`WITH TMinAndMaxSNId AS`和`CTE`语句，检查`SNId`序列是否连续。代码的核心目的是测试数据生成、删除和验证的逻辑，确保数据完整性和一致性。

————————————————————————————-
–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