SQL: найти отсутствующие идентификаторы в таблице

Ответ 1

Этот вопрос часто возникает, и, к сожалению, наиболее распространенным (и наиболее переносимым) ответом является создание временной таблицы для хранения идентификаторов, которые должны быть там, и выполнения левого соединения. Синтаксис довольно похож на MySQL и SQL Server. Единственное реальное различие - синтаксис временных таблиц.

В MySQL:

declare @id int
declare @maxid int

set @id = 1
select @maxid = max(id) from tbl

create temporary table IDSeq
(
    id int
)

while @id < @maxid
begin
    insert into IDSeq values(@id)

    set @id = @id + 1
end

select 
    s.id 
from 
    idseq s 
    left join tbl t on 
        s.id = t.id 
 where t.id is null

 drop table IDSeq

В SQL Server:

declare @id int
declare @maxid int

set @id = 1
select @maxid = max(id) from tbl

create table #IDSeq
(
    id int
)

while @id < @maxid --whatever you max is
begin
    insert into #IDSeq values(@id)

    set @id = @id + 1
end

select 
    s.id 
from 
    #idseq s 
    left join tbl t on 
        s.id = t.id 
 where t.id is null

 drop table #IDSeq

Ответ 2

Здесь запрос для SQL Server:

;WITH Missing (missnum, maxid)
AS
(
 SELECT 1 AS missnum, (select max(id) from @TT)
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN @TT tt on tt.id = Missing.missnum
WHERE tt.id is NULL
OPTION (MAXRECURSION 0); 

Надеюсь, что это будет полезно.

Ответ 3

Я знаю это старый вопрос и уже принял принятый ответ, но использование временной таблицы действительно не требуется. Исправлено форматирование (извините за двойной пост).

DECLARE @TEST_ID integer, @LAST_ID integer, @ID integer

SET @TEST_ID = 1 -- start compare with this ID 
SET @LAST_ID = 100 -- end compare with this ID

WHILE @TEST_ID <= @LAST_ID 
BEGIN 
  SELECT @ID = (SELECT <column> FROM <table> WHERE <column> = @TEST_ID) 
  IF @ID IS NULL 
  BEGIN 
    PRINT 'Missing ID: ' + CAST(@TEST_ID AS VARCHAR(10)) 
  END 
  SET @TEST_ID = @TEST_ID + 1 
END

Ответ 4

Это решение только Oracle. Он не затрагивает полный вопрос, но остается здесь для других, которые могут использовать Oracle.

select level id           -- generate 1 .. 19
from dual
connect by level <= 19

minus                     -- remove from that set

select id                 -- everything that is currently in the 
from table                -- actual table

Ответ 5

Я приземлился на этой странице, надеясь найти решение для SQLITE, поскольку это был единственный ответ, который я нашел при поиске этого же вопроса для SQLITE.

Окончательное решение, которое я нашел, было из этой статьи здесь Float Middle Blog - ответ SQLITE

Надеюсь, что это поможет кому-то другому: -)

простое решение:

SELECT DISTINCT id +1
FROM mytable
WHERE id + 1 NOT IN (SELECT DISTINCT id FROM mytable);

гений.

Ответ 6

PostgreSQL-only, вдохновленный другими ответами здесь.

SELECT all_ids AS missing_ids
FROM generate_series((SELECT MIN(id) FROM your_table), (SELECT MAX(id) FROM your_table)) all_ids
EXCEPT 
SELECT id FROM your_table

Ответ 7

В одном запросе можно найти отсутствующие идентификаторы.

SELECT distinct number

FROM master..spt_values

WHERE number BETWEEN 1 and (SELECT max(id) FROM MyTable)

AND number NOT IN (SELECT id FROM MyTable)

Ответ 8

чтобы получить недостающие строки из таблицы

DECLARE @MaxID INT = (SELECT MAX(ID) FROM TABLE1)
SELECT SeqID AS MissingSeqID
FROM (SELECT ROW_NUMBER() OVER (ORDER BY column_id) SeqID from sys.columns) LkUp
LEFT JOIN dbo.TABLE1 t ON t.ID = LkUp.SeqID
WHERE t.ID is null and SeqID < @MaxID

Ответ 9

Обновление: этот метод прошел слишком долго, поэтому я написал команду linux для поиска пробелов в текстовом файле. Он делает это в обратном порядке, поэтому сначала выгрузите весь id в текстовый файл, например:

nohup mysql --password=xx -e 'select id from tablename order by id desc' databasename > /home/ids.txt &

Первая и последняя две строки - это просто отслеживать, сколько времени прошло. 1,5 миллиона идентификаторов (ish) заняло у меня 57 секунд и это на медленном сервере. Установите максимальный id в я и на нем.

T="$(date +%s)"; \
i=1574115; \
while read line; do \
    if  [[ "$line" != "$i" ]] ; then \
        if [[ $i -lt 1 ]] ; then break; fi; \
        if  [[ $line -gt 1 ]] ; then \
            missingsequenceend=$(( $line + 1 )); \
            minusstr="-"; \
            missingsequence="$missingsequenceend$minusstr$i"; \
            expectnext=$(( $line - 1 )); \
            i=$expectnext; \
            echo -e "$missingsequence"; \
        fi; \
    else \
        i=$(( $i - 1 )); \
    fi; \
done \
< /home/ids.txt; \
T="$(($(date +%s)-T))"; \
echo "Time in seconds: ${T}"

Пример вывода:

1494505-1494507
47566-47572
Time in seconds: 57

Кроме того, я получил синтаксические ошибки с кодом из ответа Эрика, но после изменения разделителя, используя точки с запятой в соответствующих местах и ​​сохраняя его в процедуре, он работает.

Убедитесь, что вы установили правильный максимальный идентификатор, имя базы данных и имя таблицы (это в запросе выбора). И если вы хотите изменить имя процедуры, измените ее во всех трех местах.

use dbname;
drop procedure if exists dorepeat;
delimiter #
CREATE PROCEDURE dorepeat()
BEGIN
set @id = 1;
set @maxid = 1573736;
drop table if exists IDSeq;
create temporary table IDSeq
(
    id int
);

WHILE @id < @maxid DO
    insert into IDSeq values(@id);
    set @id = @id + 1;
END WHILE;

select 
    s.id 
from 
    IDSeq s 
    left join tablename t on 
        s.id = t.id 
 where t.id is null;

drop table if exists IDSeq;

END#
delimiter ;
CALL dorepeat;

Я также нашел этот запрос elwhere, но я его не тестировал.

SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM tablename AS a, tablename AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)

Ответ 10

Вставить MySQL

DELIMITER ||
DROP PROCEDURE IF EXISTS proc_missing ||
CREATE PROCEDURE proc_missing()
BEGIN 
SET @minID = (SELECT MIN(`id`) FROM `tbl_name` WHERE `user_id`=13);
SET @maxID = (SELECT MAX(`id`) FROM `tbl_name` WHERE `user_id`=13);
REPEAT 
    SET @tableID = (SELECT `id` FROM `tbl_name` WHERE `id` = @minID);
    IF (@tableID IS NULL) THEN
        INSERT INTO temp_missing SET `missing_id` = @tableID;
    END IF;
    SET @minID = @minID + 1;
UNTIL(@minID <= @maxID)
END REPEAT;
END ||
DELIMITER ;

Ответ 11

Попробуйте этот запрос. Этот единственный запрос достаточно, чтобы получить недостающие номера: (Пожалуйста, замените TABLE_NAME на имя используемого вами таблицы)

select sno as missing from(SELECT @row := @row + 1 as sno FROM 
(select 0 union all select 1 union all select 3 union all select 4 union all 
select 5 union all select 6 union all select 6 union all select 7 union all 
select 8 union all select 9) t,(select 0 union all select 1 union all select 3 
union all select 4 union all select 5 union all select 6 union all select 6 
union all select 7 union all select 8 union all select 9) t2,(select 0 
union all select 1 union all select 3 union all select 4 union all select 5 
union all select 6 union all select 6 union all select 7 union all select 8 
union all select 9) t3, (select 0 union all select 1 union all select 3 union 
all select 4 union all select 5 union all select 6 union all select 6 union all 
select 7 union all select 8 union all select 9) t4, 
(SELECT @row:=0) as b where @row<1000) as a where a.sno  not in 
  (select distinct b.no from 
(select b.*,if(@mn=0,@mn:=b.no,@mn) as min,(@mx:=b.no) as max from 
  (select ID as no from TABLE_NAME as a) as b,
        (select @mn:=0,@mx:=0) as x order by no) as b) and 
         a.sno between @mn and @mx;

Ответ 12

Несколько дней назад я работал над производственным отчетом и обнаружил, что некоторые номера отсутствуют. Недостающие числа очень важны, поэтому меня попросили найти список всех недостающих номеров для целей расследования. Я разместил здесь запись в блоге с полной демонстрацией, включая script, чтобы найти отсутствующие номера/идентификаторы в таблице образцов.

Предлагаемый script довольно длинный, поэтому я не буду включать его здесь. Ниже приведены основные шаги:

• Создайте одну временную таблицу и сохраните все отдельные номера.
• Найдите NextID, у которого что-то отсутствует. Храните в одной TempTable.
• Создайте одну временную таблицу для хранения отсутствующих деталей.
• Начните поиск отсутствующего идентификатора с помощью WHILE Loop.
• Выберите отсутствующие данные из таблицы #MissingID temp.

Ответ 13

Преобразование SQL CTE (от Paul Svirin) в версию Oracle выглядит так: (замените: YOURTABLE на имя вашей таблицы):

WITH Missing (missnum,maxid) as (
  SELECT 1 missnum, (select max(id) from :YOURTABLE) maxid from dual
  UNION ALL
  SELECT m.missnum + 1,m.maxid 
  FROM Missing m
  WHERE m.missnum < m.maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN :YOURTABLE tt on tt.id = Missing.missnum
WHERE tt.id is NULL

Ответ 14

Используя @PaulSvirin answer, я расширил его с помощью UNION, чтобы показать ВСЕ данные в моей таблице, включая отсутствующие записи с NULL s.

WITH Missing(missnum, maxid) AS
          (SELECT (SELECT MIN(tmMIN.TETmeetingID)
                   FROM tblTETMeeting AS tmMIN)
                      AS missnum,
                  (SELECT MAX(tmMAX.TETmeetingID)
                   FROM tblTETMeeting AS tmMAX)
                      AS maxid
           UNION ALL
           SELECT missnum + 1, maxid
           FROM Missing
           WHERE missnum < maxid)
SELECT missnum AS TETmeetingID,
       tt.DateID,
       tt.WeekNo,
       tt.TETID
FROM Missing LEFT JOIN tblTETMeeting tt ON tt.TETmeetingID = Missing.missnum
WHERE tt.TETmeetingID IS NULL
UNION
SELECT tt.TETmeetingID,
       tt.DateID,
       tt.WeekNo,
       tt.TETID
FROM tblTETMeeting AS tt
OPTION ( MAXRECURSION 0 )

Работайте отлично!

TETmeetingID    DateID  WeekNo  TETID
29  3063    21  1
30  null    null    null
31  null    null    null
32  null    null    null
33  null    null    null
34  3070    22  1
35  3073    23  1

Ответ 15

SELECT DISTINCT id -1
FROM users
WHERE id != 1 AND id - 1 NOT IN (SELECT DISTINCT id FROM users)

Объяснение: (id - 1)..... проверка любого предыдущего идентификатора, присутствующего в таблице

(id!= 1)..... пренебрегая, когда текущий id равен 1, поскольку его предыдущий идентификатор будет равен нулю.

Ответ 16

Эта проблема может быть решена только с помощью одного запроса

select lft.id + 1 as missing_ids
from tbl as lft left outer join tbl as rght on lft.id + 1 = rght.id
where rght.id is null and lft.id between 1 and (Select max(id)-1 from tbl)

Протестировано на Mysql

Ответ 17

Это то, что я использовал, чтобы найти недостающий идентификатор одной таблицы с именем tablename

select a.id+1 missing_ID from tablename a where a.id+1 not in (select id from tablename b where b.id=a.id+1) and a.id!=(select id from tablename c order by id desc limit 1)

Он вернет отсутствующие идентификаторы. Если есть два (2) или более непрерывных пропущенных идентификатора, он вернет только первый.