当前位置：首页 > 数据库 > mssql2008 > 详细页面

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

时间：2023-12-07来源：系统城装机大师作者：佚名

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

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60 -------------------------------------------------------------------------------------
--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