SQLServerにおいて、連番の歯抜けの最小値を取得する方法

Question

SQLServer2017です。
例えば、あるテーブルに整数型のカラム"番号"が存在するとします。
カラム"番号"には、次のような6レコードが入っていたとします。
1,2,3,6,7,8
このとき、小さい値からソートした場合4,5が抜けていますが、そのうち小さい方の’4’という値をT-SQLで取得したいです。

とりあえずfetch文を用いればできるのはわかるのですが、ループを用いる方法以外で、エレガントな書き方はあるでしょうか。

追記
SQLの例は、次の通りです。

CREATE TABLE [test_table] (
	[番号] int,
)
INSERT INTO [test_table](番号)
	VALUES(1),(2),(3),(6),(7),(8)

Comments

[Orlofsky]

質問にCREATE TABLE文と数件のテーブルに存在するデータのINSERT文も載せた方が適切なコメントが付き易いです。

[kurimaron]

追記いたしました。

Answer 1

SELECT
    MIN( 番号 + 1 ) AS 最小欠番
FROM
    test_table
WHERE
    ( 番号 + 1 ) NOT IN ( SELECT 番号 FROM test_table );

とか

SELECT
    MIN( a.番号 + 1 ) AS 最小欠番
FROM
    test_table a LEFT JOIN test_table b ON a.番号 + 1 = b.番号 
WHERE b.番号 IS NULL;

たぶん後者の方が高速

追記

連番は 1 から始まる前提で、1 がないときは 1 を返してほしい場合は、
下記のようなSQLでどうかな？

SELECT
   Min(a.番号) + 1 AS 最小欠番 
FROM
 (SELECT 番号 FROM test_table
  UNION ALL
  SELECT 0 AS 番号) a
LEFT JOIN test_table b
ON a.番号 + 1 = b.番号
WHERE b.番号 IS NULL;

データ 3,4,6,7,8 の場合でも 1 を返します。

SQLServer や Access では、FROM句を省略して書くことで、DUALテーブルと同等のことができるのを利用。

Answer 2

欠番を見つけるSQL

上記を参考に最小値を取る場合はmin()で取得

Answer 3

SQLServerで連番データ に not exists のデータの最小値を取得しては？

Answer 4

not existsとmin関数でSQLを組む。

これだけだと、データが2,3,6,7,8だった場合も「4」になる。
それで良ければ、ここまでで解決。

「1」を返して欲しいよって場合は、

SELECT文１つで完結させたいなら、CASE (Transact-SQL)を使う。

Comments

[sazi]

1が欲しい場合は、相対値を-1にすれば良いですよ。
※次の欠番：相対値=+1、前の欠番：相対値=-1

[退会済みユーザー]

saziさん、ありがとうございます。

「番号は1から」って前提なら、caseで思い切った方がイージーだと思いました。

[sazi]

caseを使用して1番を取得できますか？

[退会済みユーザー]

caseで1を返すイメージでした。

hatena19さんの「追記」のSQLがよいです。

Answer 5

not existsと自己結合だけでやるとこんな感じかなあ

with あるテーブル as (
	select 1 as 番号
	union all
	select 2 as 番号
	union all
	select 3 as 番号
	union all
	select 6 as 番号
	union all
	select 8 as 番号
)
select top 1 A.番号 as 次のない番号,A.番号+1 as 欠番
from あるテーブル as A
where not exists (
	select 0
	from あるテーブル as B
	where B.番号=A.番号+1
)
order by A.番号