Как найти "дыры" в таблице

Ответ 1

select ID +1 From Table t1
where not exists (select * from Table t2 where t1.id +1 = t2.id);

не уверен, что эта версия будет быстрее, чем предыдущая.

Ответ 2

SELECT (ID+1) FROM table AS t1
LEFT JOIN table as t2
ON t1.ID+1 = t2.ID
WHERE t2.ID IS NULL

Ответ 3

Это решение должно дать вам первое и последнее значение идентификатора "дыр", которые вы ищете. Я использую это в Firebird 1.5 на таблице 500K записей, и хотя это занимает немного времени, оно дает мне то, что я хочу.

SELECT l.id + 1 start_id, MIN(fr.id) - 1 stop_id
FROM (table l
LEFT JOIN table r
ON l.id = r.id - 1)
LEFT JOIN table fr
ON l.id < fr.id
WHERE r.id IS NULL AND fr.id IS NOT NULL
GROUP BY l.id, r.id

Например, если ваши данные выглядят следующим образом:

ID
1001
1002
1005
1006
1007
1009
1011

Вы получили бы это:

start_id   stop_id
1003       1004
1008       1008
1010       1010

Хотелось бы, чтобы я смог полностью оценить это решение, но нашел его в Xaprb.

Ответ 4

Лучший способ - создать временную таблицу со всеми идентификаторами

Затем сделайте левое соединение.

declare @maxId int
select @maxId = max(YOUR_COLUMN_ID) from YOUR_TABLE_HERE


declare @t table (id int)

declare @i int
set @i = 1

while @i <= @maxId
begin
    insert into @t values (@i)
    set @i = @i +1
end

select t.id
from @t t
left join YOUR_TABLE_HERE x on x.YOUR_COLUMN_ID = t.id
where x.YOUR_COLUMN_ID is null

Ответ 5

Недавно подумал об этом вопросе и выглядит так: это самый элегантный способ сделать это:

SELECT TOP(@MaxNumber) ROW_NUMBER() OVER (ORDER BY t1.number) 
FROM master..spt_values t1 CROSS JOIN master..spt_values t2 
EXCEPT
SELECT Id FROM <your_table>

Ответ 6

Это решение не дает всех отверстий в таблице, только следующие бесплатные - + первое доступное максимальное число в таблице - работает, если вы хотите заполнить пробелы в id-es, + получить бесплатный номер id, если у вас нет разрыв..

выберите numb + 1 из temp минус выберите numb from temp;

Ответ 7

from Как найти "пробел" ? в запуске счетчика с SQL?

select
    MIN(ID)
from (
    select
        100001 ID
    union all
    select
        [YourIdColumn]+1
    from
        [YourTable]
    where
        --Filter the rest of your key--
    ) foo
left join
    [YourTable]
    on [YourIdColumn]=ID
    and --Filter the rest of your key--
where
    [YourIdColumn] is null

Ответ 8

Это даст вам полное изображение, где "Нижний" обозначает задержка пробела и "Верх" означает пробел конец

    select *
    from 
    ( 
       (select <COL>+1 as id, 'Bottom' AS 'Pos' from <TABLENAME> /*where <CONDITION*/>
       except
       select <COL>, 'Bottom' AS 'Pos' from <TABLENAME> /*where <CONDITION>*/)
    union
       (select <COL>-1 as id, 'Top' AS 'Pos' from <TABLENAME> /*where <CONDITION>*/
       except
       select <COL>, 'Top' AS 'Pos' from <TABLENAME> /*where <CONDITION>*/)
    ) t
    order by t.id, t.Pos

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

Ответ 9

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

Полное решение вопроса следующее:

select id + 1 as newid from
    (select 100000 as id union select id from tbl) t
    where (id + 1 not in (select id from tbl)) and
          (id >= 100000)
    order by id
    limit 1;

Число 100000 должно использоваться, если первый номер последовательности равен 100001 (как в исходном вопросе); в противном случае оно должно быть соответствующим образом изменено "limit 1" используется для того, чтобы иметь только первое доступное число вместо полной последовательности

Ответ 10

Для людей, использующих Oracle, можно использовать следующее:

select a, b from (
  select ID + 1 a, max(ID) over (order by ID rows between current row and 1 following) - 1 b from MY_TABLE
) where a <= b order by a desc;