IDや通し番号などの一意になる番号を持つテーブルの登録や削除を繰り返していると、欠番(歯抜けの番号)ができる場合があります。
そんな時に客先の要望で、現時点での欠番(空き番号)の一覧が欲しいといわれることがあります。
そこで今回は、SQLでIDなどの一意に付けられた番号を持つテーブルの欠番の一覧を取得する方法を紹介します。
サンプルテーブルとデータの準備
欠番の一覧を取得するサンプルのテーブルの作成と初期データを投入します。
1 2 3 4 5 |
-- 欠番取得用のテーブルを作成 CREATE TABLE NUMBER_TABLE ( NUMBER INT NOT NULL PRIMARY KEY ); |
1 2 3 4 5 6 7 8 9 10 11 12 |
-- 欠番のあるデータを挿入 INSERT INTO NUMBER_TABLE (NUMBER) VALUES (2), (4), (5), (8), (9), (10), (14), (15); |
テーブルに登録する番号は1~15までとします。
そのうち欠番は「1, 3, 6, 7, 11, 12, 13」になります。
上記のNUMBER_TABLEテーブルへのデータのインサートは、複数のレコードを1回のSQLで一括挿入する形式で記述しています。
1回のINSERTで複数行のレコードをテーブルに一括で挿入する方法については、以下の記事を参照してください。
連番データの取得
まずは開始番号~終了番号までの連番を取得するSQLを作成します。
連番を作成するにはWITH句(共通テーブル式〔CTE: Common Table Expression〕)を利用して取得します。
以下にSQLServerでの連番を取得するSQLの例を示します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
-- 開始番号 DECLARE @START_NUMBER INT; -- 開始番号に1を指定 SET @START_NUMBER = 1; -- 終了番号 DECLARE @END_NUMBER INT -- 終了番号にNUMBER_TABLEテーブルに現在保持している最大の番号を指定 SET @END_NUMBER = (SELECT MAX(NUMBER) FROM NUMBER_TABLE); -- CTE: 共通テーブル式 WITH MISSING_NUMBER_TABLE(NUMBER_VALUE) AS ( SELECT @START_NUMBER UNION ALL SELECT NUMBER_VALUE + 1 FROM MISSING_NUMBER_TABLE WHERE NUMBER_VALUE < @END_NUMBER ) SELECT NUMBER_VALUE FROM MISSING_NUMBER_TABLE; |
WITH句で共通テーブル式を利用して1からNUMBER_TABLEテーブルに登録されている最大のNUMBERの値(15)までの連番を取得します。
上記のSQLを実行すると1~15までの行が取得できます。
欠番一覧の取得
前章の連番を取得するクエリを少し変更して、MISSING_NUMBER_TABLEのNUMBER_VALUEがNUMBER_TABLEテーブルに存在しないレコードのみ取得することで欠番の一覧が取得できます。
SQLServerで取得
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
-- 開始番号 DECLARE @START_NUMBER INT; -- 開始番号に1を指定 SET @START_NUMBER = 1; -- 終了番号 DECLARE @END_NUMBER INT -- 終了番号にNUMBER_TABLEテーブルに現在保持している最大の番号を指定 SET @END_NUMBER = (SELECT MAX(NUMBER) FROM NUMBER_TABLE); -- CTE: 共通テーブル式 WITH MISSING_NUMBER_TABLE(NUMBER_VALUE) AS ( SELECT @START_NUMBER UNION ALL SELECT NUMBER_VALUE + 1 FROM MISSING_NUMBER_TABLE WHERE NUMBER_VALUE < @END_NUMBER ) SELECT NUMBER_VALUE FROM MISSING_NUMBER_TABLE -- NUMBER_TABLEテーブルに存在しない番号のみ取得する WHERE NOT EXISTS ( SELECT * FROM NUMBER_TABLE WHERE NUMBER = NUMBER_VALUE ); |
MySQLで取得
MySQLでもWITH句(共通テーブル式)を利用すれば、SQLServerの場合と同様に欠番の一覧を取得することができます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
-- 開始番号に1を指定 SET @START_NUMBER = 1; -- 終了番号にNUMBER_TABLEテーブルに現在保持している最大の番号を指定 SET @END_NUMBER = (SELECT MAX(NUMBER) FROM NUMBER_TABLE); -- CTE: 共通テーブル式 WITH RECURSIVE MISSING_NUMBER_TABLE(NUMBER_VALUE) AS ( SELECT @START_NUMBER UNION ALL SELECT NUMBER_VALUE + 1 FROM MISSING_NUMBER_TABLE WHERE NUMBER_VALUE < @END_NUMBER ) SELECT NUMBER_VALUE FROM MISSING_NUMBER_TABLE -- NUMBER_TABLEテーブルに存在しない番号のみ取得する WHERE NOT EXISTS ( SELECT * FROM NUMBER_TABLE WHERE NUMBER = NUMBER_VALUE ); |
Oracleで取得
OracleであればWITH句を利用しなくてもLEVEL擬似列を利用して連番のデータを取得することができます。
1 2 3 4 5 6 |
SELECT * FROM (SELECT LEVEL NUM FROM DUAL CONNECT BY LEVEL <= 15) X WHERE NOT EXISTS (SELECT * FROM NUMBER_TABLE N WHERE X.NUM = N.NUMBER); |