こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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

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

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

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

投稿日時 - 2013-01-08 15:03:24

QNo.7881695

困ってます

質問者が選んだベストアンサー

実際に試せる環境が手元にないけど
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が存在していないデータの最小値を求める。
効率が悪そう。

投稿日時 - 2013-01-08 17:06:04

お礼

回答ありがとうございます。ただこの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:30

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

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

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
)
;
とか。

投稿日時 - 2013-01-09 09:33:32

お礼

回答どうもありがとうございました。
この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:08

ANo.3

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

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

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

投稿日時 - 2013-01-08 22:13:27

お礼

回答ありがとうございました。ただ、この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:27

ANo.1

SELECT MIN(CD) FROM TB WHERE CD >= 101;
でいいのではないでしょうか?

投稿日時 - 2013-01-08 15:32:36

補足

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

投稿日時 - 2013-01-08 16:11:26

あなたにオススメの質問