Ответ 1

Обновить

ConfexianMJS предоставил гораздо лучший ответ с точки зрения производительности.

Ответ (не как можно быстрее)

Здесь версия, которая работает на таблице любого размера (не только на 100 строк):

SELECT (t1.id + 1) as gap_starts_at, 
       (SELECT MIN(t3.id) -1 FROM arrc_vouchers t3 WHERE t3.id > t1.id) as gap_ends_at
FROM arrc_vouchers t1
WHERE NOT EXISTS (SELECT t2.id FROM arrc_vouchers t2 WHERE t2.id = t1.id + 1)
HAVING gap_ends_at IS NOT NULL

• gap_starts_at - первый идентификатор в текущем разрыве
• gap_ends_at - последний идентификатор в текущем разрыве

Ответ 2

Это просто помогло мне найти пробелы в таблице с более чем 80 тыс. Строк:

SELECT
 CONCAT(z.expected, IF(z.got-1>z.expected, CONCAT(' thru ',z.got-1), '')) AS missing
FROM (
 SELECT
  @rownum:[email protected]+1 AS expected,
  IF(@rownum=YourCol, 0, @rownum:=YourCol) AS got
 FROM
  (SELECT @rownum:=0) AS a
  JOIN YourTable
  ORDER BY YourCol
 ) AS z
WHERE z.got!=0;

Результат:

+------------------+
| missing          |
+------------------+
| 1 thru 99        |
| 666 thru 667     |
| 50000            |
| 66419 thru 66456 |
+------------------+
4 rows in set (0.06 sec)

Обратите внимание, что порядок expected и got столбцов является критическим.

Если вы знаете, что YourCol не начинается с 1 и это не имеет значения, вы можете заменить

(SELECT @rownum:=0) AS a

с

(SELECT @rownum:=(SELECT MIN(YourCol)-1 FROM YourTable)) AS a

Новый результат:

+------------------+
| missing          |
+------------------+
| 666 thru 667     |
| 50000            |
| 66419 thru 66456 |
+------------------+
3 rows in set (0.06 sec)

Если вам нужно выполнить какую-то задачу сценария оболочки для отсутствующих идентификаторов, вы также можете использовать этот вариант для непосредственного создания выражения, которое вы можете перебирать в bash.

SELECT GROUP_CONCAT(IF(z.got-1>z.expected, CONCAT('$(',z.expected,' ',z.got-1,')'), z.expected) SEPARATOR " ") AS missing
FROM (  SELECT   @rownum:[email protected]+1 AS expected,   IF(@rownum=height, 0, @rownum:=height) AS got  FROM   (SELECT @rownum:=0) AS a   JOIN block   ORDER BY height  ) AS z WHERE z.got!=0;

Это производит вывод как

$(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456)

Затем вы можете скопировать и вставить его в цикл for в терминале bash, чтобы выполнить команду для каждого идентификатора.

for ID in $(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456); do
  echo $ID
  # fill the gaps
done

Это то же самое, что и выше, только то, что оно читаемо и исполняемо. Изменив приведенную выше команду "CONCAT", можно создать синтаксис для других языков программирования. Или, может быть, даже SQL.

Ответ 3

Быстрый и грязный запрос, который должен сделать трюк:

SELECT a AS id, b AS next_id, (b - a) -1 AS missing_inbetween
FROM 
 (
SELECT a1.id AS a , MIN(a2.id) AS b 
FROM arrc_vouchers  AS a1
LEFT JOIN arrc_vouchers AS a2 ON a2.id > a1.id
WHERE a1.id <= 100
GROUP BY a1.id
) AS tab

WHERE 
b > a + 1

Это даст вам таблицу с идентификатором, который имеет идентификаторы, отсутствующие над ним, и next_id, которые существуют, и сколько их отсутствует между... например.

 
id  next_id  missing_inbetween
 1        4                  2
68       70                  1
75       87                 11

Ответ 4

Создайте временную таблицу со 100 строками и один столбец, содержащий значения 1-100.

Outer Присоедините эту таблицу к таблице arrc_vouchers и выберите значения одного столбца, где id arrc_vouchers равен null.

Кодирование этого слепого, но должно работать.

select tempid from temptable 
left join arrc_vouchers on temptable.tempid = arrc_vouchers.id 
where arrc_vouchers.id is null

Ответ 5

Альтернативное решение, требующее запроса + некоторый код, выполняющий некоторую обработку, будет:

select l.id lValue, c.id cValue, r.id rValue 
  from 
  arrc_vouchers l 
  right join arrc_vouchers c on l.id=IF(c.id > 0, c.id-1, null)
  left  join arrc_vouchers r on r.id=c.id+1
where 1=1
  and c.id > 0 
  and (l.id is null or r.id is null)
order by c.id asc;

Обратите внимание, что запрос не содержит подзаголовка, который, как мы знаем, он не обрабатывается с помощью планировщика MySQL.

Это приведет к возврату одной записи на centralValue (cValue), которая не имеет меньшего значения (lValue) или большего значения (rValue), то есть:

lValue |cValue|rValue
-------+------+-------
{null} | 2    | 3      
8      | 9    | {null} 
{null} | 22   | 23     
23     | 24   | {null} 
{null} | 29   | {null} 
{null} | 33   | {null} 

Не вдаваясь в подробности (мы увидим их в следующих параграфах), этот вывод означает, что:

• Нет значений между 0 и 2
• Нет значений между 9 и 22
• Нет значений между 24 и 29
• Нет значений между 29 и 33
• Нет значений между 33 и MAX VALUE

Итак, основная идея состоит в том, чтобы сделать ВПРАВО и ВЛЕВО объединяться с той же таблицей, если у нас есть значения смещений на значение (то есть: если центральное значение равно "3", тогда мы проверяем 3-1 = 2 слева и 3 +1 справа), и когда значение ROW имеет значение NULL на RIGHT или LEFT, мы знаем, что смежного значения нет.

Полный исходный вывод моей таблицы:

select * from arrc_vouchers order by id asc;

0  
2  
3  
4  
5  
6  
7  
8  
9  
22 
23 
24 
29 
33 

Некоторые примечания:

• Оператор SQL IF в условии соединения необходим, если вы определяете поле "id" как UNSIGNED, поэтому он не позволит вам уменьшить его до нуля. Это не является абсолютно необходимым, если вы сохраняете значение c.value > 0, как указано в следующем примечании, но я включаю его как документ.
• Я фильтрую нулевое центральное значение, поскольку нам не интересно какое-либо предыдущее значение, и мы можем получить значение post из следующей строки.

Ответ 6

Если вы используете MariaDB вас есть более быстрый вариант (800%) с использованием механизма хранения последовательности:

SELECT * FROM seq_1_to_50000 WHERE SEQ NOT IN (SELECT COL FROM TABLE);

Ответ 7

основанный на ответе, приведенном выше Lucek, эта хранимая процедура позволяет вам указать имена таблиц и столбцов, которые вы хотите протестировать, чтобы найти несмежные записи - таким образом, отвечая на исходный вопрос, а также демонстрируя, как можно использовать @var для представлять таблицы &/или столбцы в хранимой процедуре.

create definer=`root`@`localhost` procedure `spfindnoncontiguous`(in `param_tbl` varchar(64), in `param_col` varchar(64))
language sql
not deterministic
contains sql
sql security definer
comment ''
begin
declare strsql varchar(1000);
declare tbl varchar(64);
declare col varchar(64);

set @tbl=cast(param_tbl as char character set utf8);
set @col=cast(param_col as char character set utf8);

set @strsql=concat("select 
    ( t1.",@col," + 1 ) as starts_at, 
  ( select min(t3.",@col,") -1 from ",@tbl," t3 where t3.",@col," > t1.",@col," ) as ends_at
    from ",@tbl," t1
        where not exists ( select t2.",@col," from ",@tbl," t2 where t2.",@col," = t1.",@col," + 1 )
        having ends_at is not null");

prepare stmt from @strsql;
execute stmt;
deallocate prepare stmt;
end

Ответ 8

Хотя все это работает, результирующий набор возвращается в очень длительное время, когда есть 50 000 записей.

Я использовал это, и он нашел пробел или следующий доступный (последний использованный + 1) с гораздо более быстрым возвратом из запроса.

SELECT a.id as beforegap, a.id+1 as avail
FROM table_name a
where (select b.id from table_name b where b.id=a.id+1) is null
limit 1;

Ответ 9

Вы можете использовать серию генерации для генерации чисел от 1 до наивысшего идентификатора вашей таблицы. Затем запустите запрос, где id не в этой серии.

Ответ 10

Если существует последовательность с максимальным интервалом между двумя числами (например, 1,3,5,6), то запрос, который может быть использован, следующий:

select s.id+1 from source1 s where s.id+1 not in(select id from source1) and s.id+1<(select max(id) from source1);

• table_name - source1
• column_name - id

Ответ 11

Я попробовал это по-разному, и лучшая производительность, которую я нашел, - это простой запрос:

select a.id+1 gapIni
    ,(select x.id-1 from arrc_vouchers x where x.id>a.id+1 limit 1) gapEnd
    from arrc_vouchers a
    left join arrc_vouchers b on b.id=a.id+1
    where b.id is null
    order by 1
;

... одно соединение слева, чтобы проверить, существует ли следующий идентификатор, только если следующий, если не найден, то подзапрос находит следующий существующий идентификатор, чтобы найти конец пропуска. Я сделал это, потому что запрос с равным (=) лучше, чем оператор больше (>).

Используя sqlfiddle, он не показывает столь отличную производительность запросов других, но в реальной базе данных этот запрос выше в 3 раза быстрее, чем другие.

Схема:

CREATE TABLE arrc_vouchers (id int primary key)
;
INSERT INTO 'arrc_vouchers' ('id') VALUES (1),(4),(5),(7),(8),(9),(10),(11),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29)
;

Следуйте ниже всем запросам, которые я сделал для сравнения производительности:

select a.id+1 gapIni
    ,(select x.id-1 from arrc_vouchers x where x.id>a.id+1 limit 1) gapEnd
    from arrc_vouchers a
    left join arrc_vouchers b on b.id=a.id+1
    where b.id is null
    order by 1
;
select *, (gapEnd-gapIni) qt
    from (
        select id+1 gapIni
        ,(select x.id from arrc_vouchers x where x.id>a.id limit 1) gapEnd
        from arrc_vouchers a
        order by id
    ) a where gapEnd <> gapIni
;
select id+1 gapIni
    ,(select x.id from arrc_vouchers x where x.id>a.id limit 1) gapEnd
    #,coalesce((select id from arrc_vouchers x where x.id=a.id+1),(select x.id from arrc_vouchers x where x.id>a.id limit 1)) gapEnd
    from arrc_vouchers a
    where id+1 <> (select x.id from arrc_vouchers x where x.id>a.id limit 1)
    order by id
;
select id+1 gapIni
    ,coalesce((select id from arrc_vouchers x where x.id=a.id+1),(select x.id from arrc_vouchers x where x.id>a.id limit 1)) gapEnd
    from arrc_vouchers a
    order by id
;
select id+1 gapIni
    ,coalesce((select id from arrc_vouchers x where x.id=a.id+1),concat('*** GAT *** ',(select x.id from arrc_vouchers x where x.id>a.id limit 1))) gapEnd
    from arrc_vouchers a
    order by id
;

Может быть, это кому-то поможет и пригодится.

Вы можете увидеть и проверить мой запрос, используя этот sqlfiddle:

http://sqlfiddle.com/#!9/6bdca7/1

Ответ 12

Возможно, это не относится к делу, но я искал что-то подобное, чтобы перечислить пробелы в последовательности чисел, и нашел этот пост, в котором есть несколько разных решений, в зависимости от того, что именно вы ищете. Я искал первый доступный пробел в последовательности (то есть следующий доступный номер), и это, кажется, работает нормально.

ВЫБЕРИТЕ МИНУТУ (l.number_sequence + 1) как nextavabile от пациентов, поскольку l ВНЕШНЕЕ ОБЪЕДИНЕНИЕ пациентов, как r на l.number_sequence + 1 = r.number_sequence ГДЕ r.number_sequence равен NULL. Несколько других сценариев и решений, обсуждаемых там, с 2005 года!

Как найти пропущенные значения в последовательности с SQL

Ответ 13

Это может не работать в MySQL, но на работе (Oracle) нам нужно что-то подобное.

Мы написали Stored Proc, который принял число как максимальное значение. Затем Stored Proc создал временную таблицу с одним столбцом. Таблица содержала все числа от 1 до Max. Затем он подключил NOT IN между временной таблицей и нашей таблицей, представляющей интерес.

Если вы вызвали его с помощью Max = Select max (id) из arrc_vouchers, он затем вернет все отсутствующие значения.