其实大家稍微动下大脑，问题可以转化为，是求最小连续数组中的最大值，数组大小可以为1。
=======================================================================
做戏做全套，送佛送到西。
为了便于学习研究，必然是要写全套示例代码的。
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
附上执行计划
=======================================================================
我也不知道这代码能不能用，先发表了后续慢慢改吧。
网络代码有风险 复制粘贴需谨慎
执行这两个语句清缓存。
sql;">
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
=======================================================================
20151103-01
代码有问题 有空改
=======================================================================
20151103-02
又尝试了一下（大概几十次猜范围），发现只能处理2048以内的缺失查找。这是个敏感的数字，得研究下。
当然也可能是我不专业，写的代码有问题。
幸好不是我在开发、生产中遇到的问题，还能悠哉悠哉的分析查找问题。
这件事的启示是：你们这些讨人厌的爬虫小网站，错误代码就在这里我还就是不改了。
我保留一切法律赋予我的权利。
原文链接：https://www.f2er.com/mssql/66071.html