mysql query gesucht

[Trio]

Hi,

ich habe eine varchar-Spalte (weil auch Zeichen möglich sein sollen), in die aber hauptsächlich Zahlen geschrieben werden.

Nun möchte ich innerhalb eines Zahlenbereiches (z.b. von 500-5000) immer die nächst freie (=unbelegte) Zahl dieser Spalte ermitteln.

Bsp:
Bereich: 500-5000

Schon gewählt sind die Zahlen:
500,501,502,505,1200,4000

Dann sollte mir die Abfrage im ersten Lauf

503 als Ergebnissmenge anzeigen (dann trage ich über eine andere query diese Zahle ein)

im zweiten Lauf 504 (dann trage ich über eine andere query diese Zahle ein)

und im dritten Lauf 506.

Es geht mir, wie gesagt nur um die Query, die die Lücken ermittelt. Das Eintragen mache ich separat.

Grüße, Trio

[suit]

500,501,502,505,1200,4000

suche alle Einträge die > 500 sind, aber != 501,502,505,1200,4000

Btw: du willst doch nicht etwa IDs recyclen?

[Trio]

suche alle Einträge die > 500 sind, aber != 501,502,505,1200,4000

Du meinst, ich sollte vorab erstmal alle belegten Werte einlesen, um sie alsdann in einer where-bedingung unterzubringen?

Btw: du willst doch nicht etwa IDs recyclen?

Warum sollte ich? Da habe ich doch nach hinten hin nahezu unendlich viel Platz.

[Sven Rautenberg]

Moin!

suche alle Einträge die > 500 sind, aber != 501,502,505,1200,4000

Du meinst, ich sollte vorab erstmal alle belegten Werte einlesen, um sie alsdann in einer where-bedingung unterzubringen?

Vor allem solltest du die zunächst mal überlegen, wie das Verhalten auszusehen hat, wenn in deiner Tabelle auch Buchstaben vorkommen.

Frage 1: Wie stellst du fest, welche Zahlen schon existieren? Abfrage?

Frage 2: Was passiert, wenn in dieser Abfrage Buchstaben vorkommen?

Frage 3: Wie wird ggf. ausgeschlossen, dass Buchstaben drin vorkommen?

Frage 4: Wie werden ggf. vorkommende Buchstaben behandelt?

Mal so gesponnen: Wenn dein Bereich von 109 bis 120 gehen soll, und in der Tabelle 109, 110, 109A und 10A vorkommen - dann wäre die "nächste" Zahl welche?

Es gibt gute Begründungen für alle möglichen Werte. 111 würde gelten, wenn du dezimale Zahlen meinst und alle Buchstabenwerte ausschließt.

10B wäre korrekt, wenn du die Zahlenwerte z.B. hexadezimal betrachtest.

109B wäre korrekt, wenn die Ausstattung mit Suffixbuchstaben von A-Z hinter einer Zahl analog zu Hausnummern das Bezeichnungsschema in dieser Spalte ist.

Btw: du willst doch nicht etwa IDs recyclen?

Warum sollte ich? Da habe ich doch nach hinten hin nahezu unendlich viel Platz.

Aber irgendwas willst du tun, mit Zahlen in einer VARCHAR-Spalte, was widersinnig ist. Erkläre mal etwas mehr vom Gesamtzusammenhang, dann besteht die Chance, eine passendere Lösung zu finden als das, was du dir momentan schon ausgedacht hast.

- Sven Rautenberg

[ichbinich]

Hallo,

ich habe eine varchar-Spalte (weil auch Zeichen möglich sein sollen), in die aber hauptsächlich Zahlen geschrieben werden.

Nun möchte ich innerhalb eines Zahlenbereiches (z.b. von 500-5000) immer die nächst freie (=unbelegte) Zahl dieser Spalte ermitteln.

Das solltest du noch mal überdenken! Sobald du in dieser Spalte andere Zeichen als Zahlen eingibst, kannst du auch keine (max) Zahl mehr ermitteln...

Es geht mir, wie gesagt nur um die Query, die die Lücken ermittelt. Das Eintragen mache ich separat.

Dann solltest du dich mit max und between beschäftigen.

vg ichbinich

[Trio]

Das solltest du noch mal überdenken! Sobald du in dieser Spalte andere Zeichen als Zahlen eingibst, kannst du auch keine (max) Zahl mehr ermitteln...

Liegt leider nicht an mir. Und da die Werte historisch bedingt sind, kann ichs nicht ändern.

Grüße

[Ilja]

moin,

Nun möchte ich innerhalb eines Zahlenbereiches (z.b. von 500-5000) immer die nächst freie (=unbelegte) Zahl dieser Spalte ermitteln.

du willst quasi alle datensätze, die -> nicht <- in den zahlenbereich vorkommen und davon dann den kleinsten. das ist immer ein wenig schwieriger, etwas nicht vorhandenes aus der Datenbank heraus zu bekommen, aber nicht unmöglich.

ein weg, den ich mir vorstellen kann ist der, dass du dir alle datensätze aus dem zahlenbereich ausgibst, die keinen direkten nachfolger (+1) haben und davon den kleinsten MIN() und eben der +1. ich lasse mal das problem mit den nicht zahlen zeichen weg und tue so, als wäre es ein reiner integer wert.

SELECT MIN(t1.spalte) + 1
FROM tabelle t1
WHERE t1.spalte BETWEEN 500 AND 5000
AND NOT EXISTS (SELECT NULL
                FROM tabelle t2
                WHERE t2.spalte = t1.spalte + 1
               )
;

Ilja

[Vinzenz Mai]

Hallo Ilja,

ich lasse mal das problem mit den nicht zahlen zeichen weg und tue so, als wäre es ein reiner integer wert.

ja, die verkompliziert das Lösungsprinzip nur, so dass man es nicht auf Anhieb erkennen kann. Das Tabellendesign zu überarbeiten, wäre daher vermutlich eine gute Idee.

Die notwendige Verkomplizierung zur Ermittlung der ausschließlich gewünschten Datensätze sollte mit einer Kombination von REGEXP() und CAST() oder CONVERT() machbar sein. Ich gehe von rein dezimaler Zahlendarstellung aus.

Bevor ich es vergesse: Eine Transaktion oder Locking ist erforderlich, um keinen Time-of-check-to-time-of-use-Bug zu bauen.

Freundliche Grüße

Vinzenz

[Ilja]

moin Vinzenz,

klar, je nachdem welche werte neben zahlen noch vorhanden sind und wie genau er diese behandeln will, gibt es geeignete funktionen, die du genannt hast. erwähnen sollte man eventuell auch, dass wenn es keine datensätze in der ergebnismenge gibt, dass dann der kleinste freie wert die unter grenze ist. eventuell sollte man den NULL fall mit IFNULL abfangen und mit der untere grenze ersetzen, um immer einen wert zu bekommen.

Ilja

[Vinzenz Mai]

Hallo Ilja,

erwähnen sollte man eventuell auch, dass wenn es keine datensätze in der ergebnismenge gibt, dass dann der kleinste freie wert die unter grenze ist.

das ist der eine Sonderfall: noch gar keine Datensätze im Intervall vorhanden.
Es gibt noch einen zweiten Sonderfall: keine freie Zahl im Intervall vorhanden.

Wäre es nicht übersichtlicher, dies mit CASE zu lösen? Etwa in der Art:

SELECT  
    CASE  
    -- Intervall ist noch ganz frei  
    WHEN (SELECT  
              COUNT(DISTINCT spalte)  
          FROM  
              tabelle  
          WHERE  
              spalte BETWEEN <anfang> AND <ende>) = 0 THEN <anfang>  
    -- Intervall komplett belegt  
    WHEN (SELECT  
              COUNT(DISTINCT spalte)  
          FROM  
              tabelle  
          WHERE  
              spalte BETWEEN <anfang> AND <ende>) = (1 + <ende> - <anfang>) THEN NULL  
    -- sonst Dein Vorschlag  
    ELSE  
        ....  
    END

Die Filterung auf reine Zahlenwerte hab' ich wie Du vorhin ausgelassen. Für den Fall, dass kein Wert frei ist, gäbe ich NULL zurück - und baute aus dem ganzen gleich eine gespeicherte Funktion, die einen skalaren Wert zurückgibt :-)

Freundliche Grüße

Vinzenz

[Ilja]

moin Vinzenz,

Es gibt noch einen zweiten Sonderfall: keine freie Zahl im Intervall vorhanden.

dann wäre die ergebnismenge ebenfalls leer und das ist dann interpreationssache, ich würde sie glatt leer lassen, sprich nur einen wert zurück geben, wenn ich einen wert zurück geben kann, sonst nicht.

Wäre es nicht übersichtlicher, dies mit CASE zu lösen?

ich bin ein anhänger von CASE, weil es sehr übersichtlich ist und es so gut wie jedes DBMS kennt. allerdings würde ich es anders aufbauen, gefällt mir nicht, dass die gleiche tabelle mit den gleichen bedinungen mehrfach aufgeführt wird.

Ilja

[Vinzenz Mai]

Hallo Ilja,

Wäre es nicht übersichtlicher, dies mit CASE zu lösen?

ich bin ein anhänger von CASE, weil es sehr übersichtlich ist und es so gut wie jedes DBMS kennt. allerdings würde ich es anders aufbauen, gefällt mir nicht, dass die gleiche tabelle mit den gleichen bedinungen mehrfach aufgeführt wird.

mir auch nicht. Deswegen würde ich's in einer Stored Function vor dem CASE-Statement berechnen lassen und einer Variablen zuweisen :-)

Freundliche Grüße

Vinzenz