社内SEの徒然なる日記

SQL(Oracle)で空き番を取得したい

■ 自動採番

データ登録時に一意の番号を採番するっていうのは、よくある事です。
普通は、最大値 + 1 すれば良いでしょうし、単純な採番であればSEQUENCEを使うのが手っ取り早いです。

問題は、削除などによって採番した番号に空きが発生した時に、その空き番を再使用したい時にはどうすれば良いかってことです。

仮に、T_SEQ テーブルのMNNUMに、このようなデータがあったとします。

MNNUM
13191
13192
13193
13197
13198
13199


てっとり早いのは、MNNUM + 1 が存在しないことを確認する方法でしょうか。

SELECT MIN(A.MNNUM) + 1
FROM T_SEQ A
WHERE NOT EXISTS
( SELECT '1' FROM T_SEQ B
WHERE B.MNNUM = (A.MNNUM + 1)
)


結果、13194が返ってきます。
確かに、リスト内の空き番の最小値は持ってこれました。

NOT EXISTSを使ったのは個人的な趣味なんですが、NOT IN で書いた方が分りやすいかもしれませんね。

SELECT MIN(A.MNNUM) + 1
FROM T_SEQ A
WHERE (A.MNNUM + 1) NOT IN
( SELECT B.MNNUM FROM T_SEQ B)


■ 解説

肝になっているのは、T_SEQ テーブルのMNNUMを 1加えた値が存在するかどうかです。

そこで、副問合せを使用して、MNNUM + 1 が存在するデータを除いた結果(NOT EXISTS 又は NOT INの箇所)を取する訳です。
これで、MNNUM + 1 が使用されていないMNNUM の一覧が取得できます。

欲しいのは、空き番の最も小さい値なので、取得したMNNUM の最小値をMIN関数で取得し、1 加えた結果を返しています。

■ 最小値の判断

問題があるとすれば、リスト内の最小値が開始値でなかった場合と、そもそもリストにデータが無かった場合です。

上の例で、T_SEQ .MNNUM の開始値を1から始めたいとします。

この場合、SQL実行時点のT_SEQ .MNNUM の最小値が13191になっているため、13191未満の値が取得できません。

そして、仮にT_SEQに初めて登録する場合、SQLの実行結果は0件であり、求めている開始値 1 を取得できません。

という事で、少し知恵を絞ってみました。

SELECT MIN(A.MNNUM) + 1
FROM ( SELECT MNNUM FROM T_SEQ
UNION
SELECT 0 FROM DUAL
) A
WHERE NOT EXISTS
( SELECT '1' FROM T_SEQ B
WHERE B.MNNUM = (A.MNNUM + 1)
)


要は、既存のリストの最小値が開始値 - 1 であれば良いのですから、DUAL表を利用してダミーデータを追加してやります。

マージ方法をUNIONにすることで、T_SEQテーブルに0があったとしても(無いはずですが)、重複が除去された元リストが作成されます。

これで大丈夫...だと思う。

投稿記事の一覧:目次

■ 後書き

空き番を考慮した自動採番っていうのであれば、これで問題終了なんですが、「リストの中から空いている全ての番号を取得する」という課題には対応できません。

これは、抽出の基準が現在登録されている値に依存するため、空き番が連続していた場合に、2つめ以降の値が取得できないのです。

今回の例だと、空いているのは 13134、13195、13196の3件なのですが、13195と13196は取得できません。
これを何とかするとすれば、開始値から最大値までの連続番号を持つ仮想テーブルを作って、それとぶつけるって方法でしょうか?

何か、今一つスマートじゃないので気に入らないですね。 スポンサードリンク

PageTop

コメント


管理者にだけ表示を許可する