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

admin MsSql

2023-12-01 0 950

其实大家稍微动下大脑，问题可以转化为，是求最小连续数组中的最大值，数组大小可以为1。

=======================================================================

做戏做全套，送佛送到西。

为了便于学习研究，必然是要写全套示例代码的。

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