ある値以上の空き番の最小値を取得するSQL

テーブル TB の項目 CDがNUMBER型でユニークキーだとします。

このCDで
・ある値以上の空いている最小の番号を取得したい。
としたら１つのSQLで実現できますか？

例）
ＣＤの値が
6・・・・80　150・・・　（81から149は存在しない）
という具合に存在していた場合に、101以上で空いている最小の番号
を取得したいのです。
この場合は101です。1が取得されても駄目、81が取得されても駄目です。

また、同じ条件でデータが
6・・・・150　200・・・　（151から199は存在しない）
の場合には151が取得したいのです。

このような値を検出するSQLってわかりますか？

以上よろしくお願い致します。

No.2 ベストアンサー 回答者： nora1962 回答日時： 2013/01/08 17:06

実際に試せる環境が手元にないけど

SELECT MIN(NO)
FROM ( SELECT NO
FROM DUAL
CONNECT BY LEVEL <= ( SELECT MAX(CD) FROM TABLE_1 ) ) Q
WHERE NO >= 101
AND NOT EXISTS
( SELECT 1
FROM TABLE_1
WHERE Q.NO=TABLE_1.CD )
一旦1からCDの最大値までの連番を生成して、その中でCDが存在していないデータの最小値を求める。
効率が悪そう。

この回答へのお礼

回答ありがとうございます。ただこのSQLは最初のMIN(NO)のところで無効な識別子となりました。
実際にはここを参考に
http://d.hatena.ne.jp/yohei-a/20090819/1250663289
下記SQLにて思惑の数値の取得ができました。
-------------------------
SELECT MIN(CD) FROM (
SELECT CD FROM (SELECT LEVEL CD FROM DUAL CONNECT BY LEVEL <= 100+20000) X
WHERE NOT EXISTS (SELECT * FROM TB U WHERE X.CD = U.CD)
) WHERE CD>=100

どうもありがとうございました。

お礼日時：2013/01/09 14:23

No.4 回答者： yamada_g 回答日時： 2013/01/09 09:33

あまり考えてないので無理やり感でいっぱいですが・・・

select min(CD) + 1
from (
select CD from TB
union select (101 - 1) from DUAL)
where CD >= (101 - 1)
and (CD + 1) not in (
select CD from TB
union select (101 - 1) from DUAL
)
;
とか。

この回答へのお礼

回答どうもありがとうございました。
このSQLで100件程度のテーブルでは、実際に思惑の数値は取得できました。
ただ、UNIONの影響と思われますが、テーブルのレコード数が160000件以上で
実行してみたところ、固まってしまいました。件数が増えるとだめなようです。

実際にはここを参考に
http://d.hatena.ne.jp/yohei-a/20090819/1250663289
下記SQLにて思惑の数値の取得ができました。
-------------------------
SELECT MIN(CD) FROM (
SELECT CD FROM (SELECT LEVEL CD FROM DUAL CONNECT BY LEVEL <= 100+20000) X
WHERE NOT EXISTS (SELECT * FROM TB U WHERE X.CD = U.CD)
) WHERE CD>=100

どうもありがとうございました。

お礼日時：2013/01/09 14:28

No.3 回答者： nda23 回答日時： 2013/01/08 22:13

SQLのSは「構造」です。

論理を積み上げないと、何時までも自作
できるようになりません。

1.CDが昇順に連続しているものを考える。
2.あるCDに着目し、これを●とする。
　この●の直前の値を■とする。
　●>■である。
3.●と■の差が2以上なら、値は連続
　しておらず、抜け(空き)がある。
4.このような■に1を加算したものが求める
　値になり、これが101以上であるから
　■>=100が条件となる。

SELECT mae+1 AS empno
FROM (SELECT A.CD,MAX(B.CD) AS mae
FROM TB A LEFT JOIN TB B
ON A.CD>B.CD GROUP BY A.CD
HAVING MAX(B.CD)>=100
AND (A.CD-MAX(B.CD))>=2
ORDER BY A.CD)
WHERE ROWNUM=1

同じテーブルを２個結合し、主となる方をＡ、
従となる方をＢとします。ＢはＡより小さい
ＣＤの集団で、その中の最大が直前の値、
つまり■になるのです。勿論、A.CDが●
です。

手元にOracleが無いので試していませんが、
理論上はこれで動作すると思います。
ROWNUM=1がやや心配です。

この回答へのお礼

回答ありがとうございました。ただ、このSQLではNULLが帰ってくるため
思惑の数値が取得できませんでした。
実際にはここを参考に
http://d.hatena.ne.jp/yohei-a/20090819/1250663289
下記SQLにて思惑の数値の取得ができました。
-------------------------
SELECT MIN(CD) FROM (
SELECT CD FROM (SELECT LEVEL CD FROM DUAL CONNECT BY LEVEL <= 100+20000) X
WHERE NOT EXISTS (SELECT * FROM TB U WHERE X.CD = U.CD)
) WHERE CD>=100

どうもありがとうございました。

お礼日時：2013/01/09 14:24

No.1 回答者： kana_keiko 回答日時： 2013/01/08 15:32

SELECT MIN(CD) FROM TB WHERE CD >= 101;

でいいのではないでしょうか？

この回答への補足

回答いただいて申し訳ないのですが、「存在する最小値」ではなく「存在しない＝空いている番号の最小値」を取得したいのです。

補足日時：2013/01/08 16:11