このようなトリッキーな見出しは、かなり単純なタスクを隠しますが、最初に小さな紹介があります：
ユーザーは次のように尋ねます。「データベースにデータを入力しましたが、何が欠けているのか教えてください。 データベースにまだ入力していないデータはありますが、完全な幸福に十分ではありませんか？」
最初の（正直なところ、非常に愚かな）反応：「データベースにないものを見つけるにはどうすればよいですか？」
しかし、感情を脇に置き、ロジックを適用しましょう。 結局のところ、原則として、データが必要であり、その形成は特定のルールに従います-領収書、証明書などの数...そして、私はこれらすべての数字と識別子を自然なシーケンスに変換できるという事実から進みます。
つまり、タスクは次のように定式化されます。ギャップがある自然数のシーケンスがデータベースに格納され、ユーザーに不足している数字を表示する必要があります。
この定式化では、タスクはすでに非常に単純に見えます。 さらに、このタスクを単一のSQLクエリで実装したいという要望があります。
テーブルを作成して、データを入力してみましょう。
CREATE TABLE IF NOT EXISTS `Test` (`id` int(6) NOT NULL); INSERT INTO `Test` (`id`) VALUES (3), (5), (7), (8) , (9) , (11) , (12), (16) , (17) ;
主なアイデアは次のとおりです：テーブルをそれ自体と比較し、ICSの各値について、最小のPLAYER（ICSよりもまだ大きい）を見つけます。ここで、（ICS + 1）および（PLAY-1）は、欠落した数値範囲の境界になります。 （ICS + 1）が少なくとも（PLAYER-1）でなければならないという論理条件を追加すると、4〜4、6〜6、10〜10および13〜15の範囲が得られます。
ニュアンスは何ですか：
1）シーケンスの最初の要素はスキップされる場合があります（この場合は1です）。
2）シーケンスの最後の要素は不明です（22の場合）。 もちろん、ユーザーにこの情報を要求することもできますが、経験上、これを避ける方が良いことが示唆されています。
3）「4〜4」の範囲はバグがあるように見えます。1つの数字に置き換えるだけです
4）結果は、行のセットではなく、単一の行の値を取得するために依然として望ましいです
コメントを考慮して、MySQL用のスクリプトのバージョンを取得します。
SELECT GROUP_CONCAT( ranges ) FROM ( SELECT CASE WHEN id2 IS NULL THEN CONCAT( id1, '...' ) WHEN id1 = id2 THEN id1 ELSE CONCAT( id1, '-', id2 ) END ranges FROM ( SELECT id +1 id1, ( SELECT MIN( id ) -1 FROM `Test` t2 WHERE t2.id > t1.id )id2 FROM `Test` t1 UNION SELECT 1 , MIN( id ) -1 FROM `Test` t3 )t WHERE id1 <= id2 OR id2 IS NULL ORDER BY id1 )tt
Oracleのオプション：
SELECT LISTAGG (ranges, ', ') FROM ( SELECT CASE WHEN id2 IS NULL THEN TO_CHAR (id1) || '...' WHEN id1 = id2 THEN TO_CHAR (id1) ELSE TO_CHAR (id1) || '-' || TO_CHAR (id2) END ranges FROM ( SELECT id + 1 id1, (SELECT MIN (id) - 1 FROM TEST t2 WHERE t2.id < t1.id) id2 FROM TEST t1 UNION SELECT 1, MIN (id) - 1 FROM TEST t3) t WHERE id1 <= id2 OR id2 IS NULL ORDER BY id1 ) tt
実行の結果は、ストリング「1-2、4、6、10、13-15、18 ...」です。
まず、この行にはユーザーが望んでいたものが含まれています。
第二に、結果はどのユーザーにとっても明らかです。
そして基本的に、クエリは実際にデータベースに保存されていないデータを表示します！
UPD1：
スクリプトを改善するためのオプションを提案し、この問題に関心を示したすべての人に感謝します。
よくあることですが、コメントは元の記事よりも興味深く有用であることがわかりました。
実際、ニュアンス番号4は標準のSQL構文によって実装されていないことは注目に値します。このタスクでは、個々のDBMSに独自の方法で実装される追加機能を引き付ける必要があります。
以下は、この記事のコメントから私が描いたスクリプトです。
asmmの MySQLオプション
SELECT CONCAT(IFNULL(CONCAT(GROUP_CONCAT(miss_num), ','), '') , IFNULL(MAX(id) + 1, @start_num) , '...' ) miss_num FROM ( SELECT @prev_id prev_id , CASE WHEN @prev_id + 1 = id THEN NULL WHEN @prev_id + 2 = id THEN @prev_id + 1 ELSE CONCAT(@prev_id + 1, '-', id - 1) END miss_num , @prev_id := id id FROM (SELECT @start_num := 1 start_num, @prev_id := @start_num - 1 prev_id) p , `Test` t WHERE t.id >= p.start_num ORDER BY t.id ) t
xtenderからのOracleのオプション
select listagg(id1||decode(id2 ,id1 ,null ,null,'...' ,'-'||id2) ,',') within group(order by id1)s from (select max(id)+1 id1 ,lead(min(id)) over(order by min(id)) id2 from (select 0 id, 0 rn from dual union all select id,row_number()over(order by id) rn from test) group by id - rn)
yizraorの MSSQLのオプション
select rlist = ( select "text()" = iif(id1 < id2, convert(varchar(15), id1) + iif(id1 < (id2 - 1), '-' + convert(varchar(15), id2 - 1), ''), '') + iif(id3 is null, iif(id1 < id2, ', ', '') + convert(varchar(15), id2 + 1) + '...', ', ') from ( select id1 = isnull(lag(id) over (order by id), 0) + 1, id2 = id, id3 = lead(id) over (order by id) from test ) t where ( id1 < id2 ) or ( id3 is null ) order by id2 for xml path('') )