SQL Server 2008 R2——查找最小nIndex,nIndex存在而nIndex+1不存在 求最小连续数组中的最大值_mssql2008_

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

 DBCC FREEPROCCACHE GO DBCC DROPCLEANBUFFERS GO