"Löcher" suchen

6 views
Skip to first unread message

Alexander Slanina

unread,
Nov 29, 2005, 11:43:50 AM11/29/05
to
Hallo Leute !

Ich habe folgende Frage:

wie kann ich in meiner Datenbank, von einem Feld (primary key,
integer, ca. 5700 Einträge) die "Löcher" also fehlende Nummern suchen
und ausgeben lassen ?

Danke

Alex.

Dominik Echterbruch

unread,
Nov 29, 2005, 11:52:09 AM11/29/05
to

Leg eine Tabelle an, in der alle Zahlen drin stehen (ohne Löcher) und
mach einen LEFT JOIN.
Nur: Welchen Sinn soll das machen?


Grüße,
Dominik
--
MonstersGame - Die Schlacht zwischen Vampiren und Werwölfen
http://spielwelt6.monstersgame.net/?ac=vid&vid=3018786

Harald Stowasser

unread,
Nov 29, 2005, 12:51:31 PM11/29/05
to
Alexander Slanina schrieb:

<*WICHTIG*>
Wenn dein PK ein auto_increment Feld ist, dann *möchtest* du das gar
nicht! Weil diese Spalte keinerlei Bedeutung haben soll^Wdarf! Und nur
als Handle gebraucht wird!
</*WICHTIG*>

Bei natürlichen PKs würde ich das eher so ähnlich lösen:

#Testtabelle anlegen:
CREATE TABLE tab (
pk INTEGER UNSIGNED NOT NULL,
PRIMARY KEY(pk)
);

#Mit Testdaten füllen
INSERT INTO tab SET pk=1;
INSERT INTO tab SET pk=2;
INSERT INTO tab SET pk=4;
INSERT INTO tab SET pk=5;
INSERT INTO tab SET pk=9;
INSERT INTO tab SET pk=11;
INSERT INTO tab SET pk=12;

#Zeigt alle Datensätze die keinen Nachfolger haben:
SELECT a.pk
FROM tab a
LEFT JOIN tab b ON b.pk=a.pk+1
WHERE b.pk is null;


Sven Paulus

unread,
Nov 29, 2005, 1:40:18 PM11/29/05
to
Harald Stowasser <stowa...@idowa.de> wrote:
> Bei natürlichen PKs würde ich das eher so ähnlich lösen:
> #Testtabelle anlegen:
> CREATE TABLE tab (
> pk INTEGER UNSIGNED NOT NULL,
> PRIMARY KEY(pk)
> );
> #Mit Testdaten füllen
> INSERT INTO tab SET pk=1;
> INSERT INTO tab SET pk=2;
> INSERT INTO tab SET pk=4;
> INSERT INTO tab SET pk=5;
> INSERT INTO tab SET pk=9;
> INSERT INTO tab SET pk=11;
> INSERT INTO tab SET pk=12;

> #Zeigt alle Datensätze die keinen Nachfolger haben:
> SELECT a.pk
> FROM tab a
> LEFT JOIN tab b ON b.pk=a.pk+1
> WHERE b.pk is null;

Das kann aber keine mehrere aufeinanderfolgende Lueckenelemente
aufzeigen.

Wie waer's mit einer komplett uneleganten Brute-Force-Loesung mit
einer Stored Procedure (auf obiges aufbauend):

# temporaere Tabelle um die Liste der Luecken speichern
DROP TABLE IF EXISTS missing;
CREATE TEMPORARY TABLE missing (pk INTEGER UNSIGNED) TYPE=Memory;

# stored procedure definieren ...
DROP PROCEDURE IF EXISTS findmissing;
DELIMITER |
CREATE PROCEDURE findmissing ()
BEGIN
DECLARE mpk INTEGER;
DECLARE i INTEGER;
SET mpk := (SELECT MAX(pk) FROM tab);
SET i := 1;
REPEAT
BEGIN
IF (SELECT COUNT(*) FROM tab WHERE pk=i) = 0 THEN
INSERT INTO missing VALUES (i);
END IF;
SET i := i+1;
END;
UNTIL i > mpk
END REPEAT;
END
|
DELIMITER ;

# ... und aufrufen
CALL findmissing();

# letztendlich Ergebnis anzeigen
SELECT * FROM missing;

Wenn es um eine einmalige Aufraeumaktion geht, wuerde ich aber dann
doch auf Unix-Shell-Ebene ein

echo "SELECT pk FROM tab ORDER BY pk;" | mysql bla | sed '1d' > a
seq 1 `tail -1 a` > b
comm -1 -3 a b

machen. Das ist das einfachste.

Sven Paulus

unread,
Nov 29, 2005, 1:57:08 PM11/29/05
to
Sven Paulus <sv...@karlsruhe.org> wrote:
> Wie waer's mit einer komplett uneleganten Brute-Force-Loesung mit
> einer Stored Procedure (auf obiges aufbauend):

Aeh, ok, mit Cursors ist's dann doch eleganter:

DROP TABLE IF EXISTS missing;
CREATE TEMPORARY TABLE missing (pk INTEGER UNSIGNED) TYPE=Memory;

DROP PROCEDURE IF EXISTS findmissing;


DELIMITER |
CREATE PROCEDURE findmissing ()
BEGIN

DECLARE i INTEGER UNSIGNED DEFAULT 1;
DECLARE lv INTEGER;
DECLARE goon INTEGER DEFAULT 1;
DECLARE cur CURSOR FOR SELECT pk FROM tab ORDER BY pk;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET goon = 0;

OPEN cur;
FETCH cur INTO lv;

WHILE goon DO
BEGIN
IF i < lv THEN


INSERT INTO missing VALUES (i);

ELSE
FETCH cur INTO lv;


END IF;
SET i := i+1;
END;

END WHILE;
CLOSE cur;
END
|
DELIMITER ;

CALL findmissing();

SELECT * FROM missing;

Stefan Rybacki

unread,
Nov 29, 2005, 5:29:30 PM11/29/05
to
Sven Paulus wrote:
> Harald Stowasser <stowa...@idowa.de> wrote:
>
>>Bei natürlichen PKs würde ich das eher so ähnlich lösen:
>>#Testtabelle anlegen:
>>CREATE TABLE tab (
>> pk INTEGER UNSIGNED NOT NULL,
>> PRIMARY KEY(pk)
>>);
>>#Mit Testdaten füllen
>>INSERT INTO tab SET pk=1;
>>INSERT INTO tab SET pk=2;
>>INSERT INTO tab SET pk=4;
>>INSERT INTO tab SET pk=5;
>>INSERT INTO tab SET pk=9;
>>INSERT INTO tab SET pk=11;
>>INSERT INTO tab SET pk=12;
>
>
>>#Zeigt alle Datensätze die keinen Nachfolger haben:
>>SELECT a.pk
>> FROM tab a
>> LEFT JOIN tab b ON b.pk=a.pk+1
>> WHERE b.pk is null;
>
>
> Das kann aber keine mehrere aufeinanderfolgende Lueckenelemente
> aufzeigen.

diese könnte die Bereiche aufzeigen:

SELECT h1.pk, min(h2.pk)
FROM tab h1 JOIN tab h2 ON (h1.pk<h2.pk) GROUP BY h1.pk HAVING (h1.pk<min(h2.pk)-1)

Bis denn dann
Stefan

Andreas Kretschmer

unread,
Nov 30, 2005, 1:45:47 AM11/30/05
to
begin Alexander Slanina schrieb:

Also sowas hier:

test=# select * from luecke ;
id
----
1
2
4
5
10
(5 rows)

test=# select x from generate_series(1,(select max(id) from luecke)) x where x not in (select id from luecke );
x
---
3
6
7
8
9
(5 rows)


Falls generate_series() nicht verfügbar ist, brauchst Du halt eine extra
Tabelle.


end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Sven Paulus

unread,
Nov 30, 2005, 3:41:25 AM11/30/05
to
Andreas Kretschmer <andreas_k...@despammed.com> wrote:
> Falls generate_series() nicht verfügbar ist, brauchst Du halt eine extra
> Tabelle.

Es ist nicht verfuegbar, wie Du mit einem leichten Blick in die
Dokumentation haettest feststellen koennen. Was bringt es, wenn Du
hier Phantasiefunktionen beschreibst, die es nicht gibt?

Markus Mann

unread,
Nov 30, 2005, 5:14:45 AM11/30/05
to

Er kann so anschaulich vor Augen führen, dass seiner länger ist.

fup2p
--
Markus Mann <http://www.max93.de/>
];-)

Andreas Kretschmer

unread,
Nov 30, 2005, 5:24:07 AM11/30/05
to
begin Sven Paulus schrieb:

> Andreas Kretschmer <andreas_k...@despammed.com> wrote:
>> Falls generate_series() nicht verfügbar ist, brauchst Du halt eine extra
>> Tabelle.
>
> Es ist nicht verfuegbar, wie Du mit einem leichten Blick in die
> Dokumentation haettest feststellen koennen. Was bringt es, wenn Du

1. kann MySQL ja Funktionen, damit kann man sowas nachbauen. Ist sehr
nützlich, das haben andere hier schon bestätigt.
2. ich nannte eine Alternative.


> hier Phantasiefunktionen beschreibst, die es nicht gibt?

wie schon gesagt, es sollte trivial sein, sich eine Funktion zu
schreiben, die dies tut. Andere Systeme haben es, IMHO auch Oracle.

Alexander Slanina

unread,
Dec 7, 2005, 4:43:28 AM12/7/05
to
On Tue, 29 Nov 2005 17:52:09 +0100, Dominik Echterbruch
<new...@crosslight.de> wrote:

>Leg eine Tabelle an, in der alle Zahlen drin stehen (ohne Löcher) und
>mach einen LEFT JOIN.
>Nur: Welchen Sinn soll das machen?

Ich habe eine db mit Literaturdaten, und der Vorgänger hat anscheinend
nicht alle eingegeben, jetzt suche ich ein einfaches Mittel die
Fehlenden zu finden.

lg

Alex.

Alexander Slanina

unread,
Dec 7, 2005, 4:44:43 AM12/7/05
to
On Tue, 29 Nov 2005 18:51:31 +0100, Harald Stowasser
<stowa...@idowa.de> wrote:

><*WICHTIG*>
>Wenn dein PK ein auto_increment Feld ist, dann *möchtest* du das gar
>nicht! Weil diese Spalte keinerlei Bedeutung haben soll^Wdarf! Und nur
>als Handle gebraucht wird!
></*WICHTIG*>

Richtig, ist es aber nicht. Es wird programmässig erhöht bzw.
überprüft auf uniqueness.

lg

Alex.


Alexander Slanina

unread,
Dec 7, 2005, 4:46:00 AM12/7/05
to
On Tue, 29 Nov 2005 23:29:30 +0100, Stefan Rybacki
<stefan....@gmx.net> wrote:

Herzlichen Dank an alle die geholfen haben, diese Lösung hier werde
ich probieren !

lg

Alex.

Reply all
Reply to author
Forward
0 new messages