Verwirrung beim Query

[Kirby.exe]

Also ich habe folgendes Problem: Ich habe eine Datenbank Tabelle "Box", welche eine Lagerbox modelliert. Diese hat die Werte x,y und z um im Raum angeordnet abgebildet zu werden und ebenfalls eine storage_id (Fremdschlüssel auf Storage Tabelle). Nun möchte ich in einem Query gerne alle Boxen welche jeweils die höchsten auf ihrem Stappel sind (also max(z)) und ebenfalls z.B. nur im Storage mit der ID 1 liegen.

Meine Idee war, dass ich ein Temp View erstellen, wo ich alle boxen reinlege welche immer noch eine "höhere" Box haben und somit nicht die Top Box sind. Dann möchte ich in einem anderen Query alle Boxen selecten, welche nicht in dem Unterquery sind mittel NOT EXISTS. Leider bekomme ich schon beim Hilfsview keinen Output in PHPMYADMIN

CREATE TEMPORARY TABLE temp
    SELECT DISTINCT b2.x, b2.y, b2.z, b2.sort_id, b2.storage_id FROM box b1, box b2
    WHERE b1.x = b2.x
        AND b1.y = b2.y
        AND b1.z > b2.z
        AND b1.storage_id = 1
        AND b2.storage_id = 1;
        
Select * FROM temp;

 

[mihe7]

Spontan würde ich sagen:

Höchste Koordinate im Stapel: SELECT storage_id,x,y,max(z) as maxZ FROM box GROUP BY storage_id,x,y;

Box mit höchster Koordinate:

SELECT box.*
  FROM box INNER JOIN (
      SELECT storage_id,x,y,max(z) AS maxZ
       FROM box
     GROUP BY storage_id,x,y) coord
  ON box.storage_id=coord.storage_id AND box.x = coord.x AND box.y = coord.y AND box.z = coord.maxZ

und wenn Du nur die storage_id = 1 willst, haust Du halt noch die WHERE-Klausel hintendran.

 

[Kirby.exe]

Wow das zeigt mal wieder, dass ich wohl meine SQL Kenntnisse auffrischen sollte xD

 

[mihe7]

Wow das zeigt mal wieder, dass ich wohl meine SQL Kenntnisse auffrischen sollte xD

Apropos auffrischen: das ist ein Uralt-Standardpattern, das ich seit 20 Jahren verwende, kann sein, dass es da mittlerweile bessere Möglichkeiten gibt

Ich lese gerade was von phpmyadmin. MySQL ist z. T. richtig grottig bei der Query-Optimization. Da würde es sich ggf. empfehlen die Einschränkung auf die storage_id schon bzw. zusätzlich innerhalb des Subselects zu machen. Müsstest Du Dir mal im Ausführungsplan anschauen.

 

[Oneixee5]

Apropos auffrischen: das ist ein Uralt-Standardpattern, das ich seit 20 Jahren verwende, kann sein, dass es da mittlerweile bessere Möglichkeiten gibt

Ich lese gerade was von phpmyadmin. MySQL ist z. T. richtig grottig bei der Query-Optimization. Da würde es sich ggf. empfehlen die Einschränkung auf die storage_id schon bzw. zusätzlich innerhalb des Subselects zu machen. Müsstest Du Dir mal im Ausführungsplan anschauen.

Es stimmt, die Abfrage optimiert sehr schlecht. Diese Self-Join-Subselect-Konstrukte sollte man tunlichst vermeiden. Ich denke man könnte hier sehr gut mit einer Window-Funktion optimieren.