SQL: trova gli ID mancanti in una tabella

Ho una tabella con una chiave primaria auto-incrementale univoca. Nel tempo, le voci possono essere cancellate dalla tabella, quindi ci sono “buchi” nei valori di questo campo. Ad esempio, i dati della tabella possono essere i seguenti:

ID | Value | More fields... --------------------------------- 2 | Cat | ... 3 | Fish | ... 6 | Dog | ... 7 | Aardvark | ... 9 | Owl | ... 10 | Pig | ... 11 | Badger | ... 15 | Mongoose | ... 19 | Ferret | ... 

Sono interessato a una query che restituirà l’elenco degli ID mancanti nella tabella. Per i dati di cui sopra, i risultati attesi sono:

  ID ---- 1 4 5 8 12 13 14 16 17 18 

Gli appunti:

  1. Si presume che il primo ID iniziale fosse 1
  2. L’ID massimo che deve essere esaminato è quello finale, vale a dire che va bene supporre che non ci siano voci aggiuntive dopo l’ultima corrente (vedere i dati aggiuntivi su questo punto di seguito)

Uno svantaggio dei requisiti precedenti è che l’elenco non restituirà gli ID creati dopo l’ID 19 e che sono stati eliminati. Attualmente sto risolvendo questo caso in codice, perché tengo il massimo ID creato. Tuttavia, se la query può assumere come parametro MaxID, e anche restituire quegli ID tra il massimo corrente e MaxID, questo sarebbe un bel “bonus” (ma certamente non un must).

Attualmente sto lavorando con MySQL, ma prendere in considerazione il passaggio a SQL Server, quindi mi piacerebbe che la query fosse adatta a entrambi. Inoltre, se si utilizza qualcosa che non può essere eseguito su SQLite, si prega di menzionarlo, grazie.

Questa domanda spesso si presenta e, purtroppo, la risposta più comune (e più portabile) è quella di creare una tabella temporanea per contenere gli ID che dovrebbero essere lì, e fare un join sinistro. La syntax è abbastanza simile tra MySQL e SQL Server. L’unica vera differenza è la syntax delle tabelle temporanee.

In 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 

In 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 

Ecco la query per 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); 

Spero che questo sia utile.

Sono atterrato su questa pagina sperando di trovare una soluzione per SQLITE in quanto questa era l’unica risposta che ho trovato durante la ricerca di questa stessa domanda per SQLITE.

La soluzione finale che ho trovato era da questo articolo qui Float Middle Blog – risposta SQLITE

Spero che aiuti qualcun altro fuori 🙂

la soluzione semplice è:

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

genio.

So che è una vecchia domanda e ha già una risposta accettata, ma l’utilizzo di una tabella temporanea non è realmente necessario. Risolto il problema di formattazione (scusa per il doppio post).

 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  FROM  WHERE  = @TEST_ID) IF @ID IS NULL BEGIN PRINT 'Missing ID: ' + CAST(@TEST_ID AS VARCHAR(10)) END SET @TEST_ID = @TEST_ID + 1 END

Questa è una soluzione solo Oracle. Non affronta la domanda completa, ma è lasciata qui per altri che potrebbero utilizzare 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 

Solo PostgreSQL, ispirato ad altre risposte qui.

 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 

La singola query può trovare gli ID mancanti.

 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) 

per ottenere le righe mancanti dalla tabella

 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 

Aggiornamento: questo metodo ha impiegato troppo tempo, quindi ho scritto un comando linux per trovare le lacune in un file di testo. Lo fa in ordine inverso, quindi prima scarica tutti gli id ​​in un file di testo come questo;

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

La prima e l’ultima riga servono solo per tenere traccia del tempo impiegato. 1,5 milioni di ID (ish) mi hanno portato a 57 secondi e questo è su un server lento. Imposta l’ID max in I e disponili.

 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}" 

Esempio di output:

 1494505-1494507 47566-47572 Time in seconds: 57 

Inoltre, ho ricevuto errori di syntax con il codice dalla risposta di Eric, ma dopo aver modificato il delimitatore, usando il punto e virgola nei punti appropriati e archiviandolo in una procedura, funziona.

Assicurati di aver impostato l'ID massimo, il nome del database e il nome della tabella (è nella query di selezione). E se vuoi cambiare il nome della procedura, cambialo in tutti e 3 i posti.

 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; 

Ho anche trovato questa query elwhere, ma non l'ho ancora testata.

 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) 

PROVA in 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 ; 

Prova questa domanda. Questa singola query è sufficiente per ottenere numeri mancanti: (Sostituisci TABLE_NAME con il nome della tabella che stai utilizzando)

 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; 

Qualche giorno fa stavo lavorando a un rapporto di produzione e ho trovato alcuni numeri mancanti. I numeri mancanti sono molto importanti, quindi mi è stato chiesto di trovare un elenco di tutti i numeri mancanti a fini di indagine. Ho pubblicato un post sul blog qui, con una demo completa, incluso uno script per trovare numeri / ID mancanti in una tabella di esempio.

La sceneggiatura suggerita è piuttosto lunga, quindi non la includerò qui. Ecco i passaggi di base utilizzati:

  1. Crea una tabella temporanea e memorizza tutti i numeri distinti.
  2. Trova NextID che ha qualcosa che manca prima. Memorizza in un’unica TempTable.
  3. Crea una tabella temporanea per memorizzare i dettagli del numero mancante.
  4. Inizia a trovare l’id mancante usando il ciclo WHILE.
  5. Seleziona i dati mancanti dalla tabella temp #MissingID.

Conversione di SQL CTE (da Paul Svirin) alla versione di Oracle è simile a questa (sostituisci: YOURTABLE con il nome della tua tabella):

 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 

Usando la risposta di @PaulSvirin , l’ho estesa con UNION per mostrare TUTTI i dati nella mia tabella, inclusi i record mancanti con NULL .

 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 ) 

Funziona alla grande!

 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 
 SELECT DISTINCT id -1 FROM users WHERE id != 1 AND id - 1 NOT IN (SELECT DISTINCT id FROM users) 

Spiegazione: (id – 1) ….. verificando eventuali ID precedenti presenti nella tabella

(id! = 1) ….. trascurando quando l’id corrente è 1 come il suo id precedente sarà 0 zero.

Questo problema può essere risolto con una sola query

 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) 

Testato su Mysql

Questo è quello che ho usato per trovare l’id mancante di una tabella denominata 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)

Restituirà gli ID mancanti. Se ci sono due (2) o più ID mancanti continui, restituirà solo il primo.