Под таким хитрым заголовком скрывается достаточно несложная задача, но сначала небольшое вступление:


Приходят пользователи и просят: «Вот мы внесли данные в базу, а скажите нам, чего не хватает? Какие данные мы ещё не внесли в базу и их не хватает для полного счастья?»
Первая (и скажем честно, весьма глупая) реакция: «Как же я вам найду то, чего нет в базе данных?».


Но отбросим эмоции и применим логику. Ведь, как правило, требуются данные, формирование которых подчиняется некоему правилу — номера квитанций, справок и так далее… И я исхожу из того, что все эти номера и идентификаторы могут быть преобразованы в натуральную последовательность.
То есть задача будет сформулирована следующим образом: в базе данных хранится последовательность натуральных чисел, в которой есть пропуски, и необходимо вывести пропущенные числа для пользователя.
В такой формулировке задача уже выглядит достаточно простой. Более того — возникает желание реализовать эту задачу одним единственным 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) ;

Основная идея следующая: сравнить таблицу с самой собой же и для каждого значения ИКС найти минимальное ИГРЕК (которое всё же больше ИКСа), где (ИКС + 1) и (ИГРЕК — 1) будут нашими границами пропущенных диапазонов чисел. Добавив логичное условие, что, (ИКС + 1) должен быть не меньше (ИГРЕК — 1) получим следующие диапазоны: от 4 до 4, от 6 до 6, от 10 до 10 и от 13 до 15.
Какие есть нюансы:
1) Может быть пропущен первый элемент последовательности (в нашем случае это 1)
2) Неизвестен последний элемент последовательности (а вдруг это 22). Можно, конечно, запрашивать эту информацию у пользователя, но опыт подсказывает, что лучше этого избегать.
3) Диапазон «от 4 до 4» выглядит глючно, надо заменить просто на одно число
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 и для этой задачи необходимо привлекать дополнительный функционал, реализуемый на каждой отдельной СУБД по своему.
Ниже привожу скрипты, вытянутые мной из комментариев к статье.


Вариант для MySQL от asmm 


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

Вариант для Oracle от xtender


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)

Вариант для MSSQL от yizraor


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('')
)