（元）社内SEの徒然なる日記

SQL（Oracle）で空き番を取得したい

2013-08-28（Wed）

■　自動採番

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

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

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

てっとり早いのは、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の実行結果は０件であり、求めている開始値 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があったとしても（無いはずですが）、重複が除去された元リストが作成されます。

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

投稿記事の一覧：目次

■　後書き

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

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

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

何か、今一つスマートじゃないので気に入らないですね。