SQLクエリメモ - 整数型カラム中の欠番を見つけるクエリ
とあるプログラム(何)で、未処理のID(=欠番)を見つけ出して処理を実行するという必要性が生じたので、欠番を見つけ出すためのSQLクエリを作ってみた。(プログラムでループ回して1つずつチェックなんて、数万件のデータに対してはやってられないから)
プログラムが使用しているのがDB2だったので、DB2でのクエリ。
WITH temp(num, num2) AS ( SELECT num, num-1 FROM hoge WHERE num-1 NOT IN (SELECT num FROM hoge) AND num-1 >= (SELECT MIN(num) FROM hoge) UNION ALL SELECT num, num2-1 FROM temp WHERE num2-1 NOT IN (SELECT num FROM hoge) AND num2-1 >= (SELECT MIN(num) FROM hoge) ) SELECT temp.num2 FROM temp ORDER BY temp.num2
たとえばこんなテーブル:
CREATE TABLE hoge(num INTEGER NOT NULL PRIMARY KEY) INSERT INTO hoge(num) VALUES ( 2), ( 3), ( 4), ( 5), ( 6) , ( 7), (10), (11), (13), (14) , (17), (19), (22), (23), (26) , (29), (31), (34), (37), (38) , (41), (43), (46), (47)
を作って実行すると、
NUM2 ----------- 8 9 12 15 16 18 20 21 24 25 27 28 30 32 33 35 36 39 40 42 44 45
また、上記のクエリだと、欠番すべてが返されるため、実際には既存の番号より1だけ小さい欠番だけを取得するクエリを使用している(最初に作ったのはこっち)。
WITH temp(num, num2) AS ( SELECT num, num-1 FROM hoge WHERE num-1 NOT IN (SELECT num FROM hoge) AND num-1 >= (SELECT MIN(num) FROM hoge) ) SELECT temp.num2 FROM temp ORDER BY temp.num2
こちらのクエリの場合、結果は次の通り。
NUM2 ----------- 9 12 16 18 21 25 28 30 33 36 40 42 45
ちなみに、全然関係ないが、テーブルhogeに含まれているのはどんな数値の列でしょう。(ヒント:2つの数列が混在しています)