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


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