一个数据库问题--寻找T1表中keycol列缺少的最小正整数

INSERT INTO dbo.T1(keycol, datacol) VALUES(4, 'b');

INSERT INTO dbo.T1(keycol, datacol) VALUES(6, 'c');

INSERT INTO dbo.T1(keycol, datacol) VALUES(7, 'd');

--looking for the minimum missing integer for dbo.T1 keycol column

SELECT 

CASE

WHEN NOT EXISTS( SELECT * FROM dbo.T1 WITH(NOLOCK) WHERE keycol=1) THEN 1

ELSE (SELECT min(1+A.keycol) 

 FROM dbo.T1 AS A  WITH(NOLOCK)

 WHERE NOT EXISTS

( SELECT * 

 FROM dbo.T1 AS B WITH(NOLOCK) 

 WHERE A.keycol+1=B.keycol))

    END;

SELECT MIN(A.keycol + 1) 

FROM dbo.T1 AS A LEFT OUTER JOIN dbo.T1 AS B ON B.keycol = A.keycol + 1 

WHERE B.keycol IS NULL;

DECLARE @min INT, @max INT

SET @min = 1

SELECT  @max =max(keycol)+1 FROM dbo.T1 (NOLOCK)

print @min

print @max

while( @min < @max )

BEGIN;

IF NOT EXISTS(SELECT * FROM dbo.T1 (NOLOCK) WHERE keycol=@min)

BEGIN;

print @min;

break;

END;

ELSE

SET @min = @min+1

END;

转载于:https://www.cnblogs.com/shawnliu/archive/2009/09/13/1565803.html