連番の中で「抜け番」見つけるSQL

2018/09/072021/02/04データベースSQLServer,Oracle

行の番号などに自動で採番していった値で、使わなくなって「抜け番」になってしまうことがあります。

そんな時にその「抜け番」を再利用するために「抜け番」の一番小さな値を検索するSQLです。

1. テーブル内の連番の抜け番を探す
2. まとめ

テーブル内の連番の抜け番を探す

前準備としてテーブルを作成し、連番の「3」が抜けたデータを挿入します。

--テーブルの準備
CREATE TABLE test_table
(
    no  int
)
 
--データの準備
INSERT INTO test_table VALUES ( 1 )
INSERT INTO test_table VALUES ( 2 )
INSERT INTO test_table VALUES ( 4 )
INSERT INTO test_table VALUES ( 5 )

準備ができたら以下のSQLを実行して抜け番を取得します。

--歯抜けの番号を探す
SELECT
    MIN( no + 1 ) AS 抜け番
FROM
    test_table
WHERE
    ( no + 1 ) NOT IN ( SELECT no FROM test_table )

以下が実行した結果です。

抜け番
3

抜け番がなかった場合は現在存在する番号の最大値＋１が返されます。
（上記で準備したテーブルに抜け番「３」を埋めます）

--データの準備
INSERT INTO test_table VALUES ( 3 )
 
--歯抜けの番号を探す
SELECT
    MIN( no + 1 ) AS 抜け番
FROM
    test_table
WHERE
    ( no + 1 ) NOT IN ( SELECT no FROM test_table )