Fälle bei WHERE unterscheiden

[deemon84]

Hallo,

ich arbeite an einem Projekt und hänge seit einiger Zeit bei einer SQL Query. Die Tabelle sieht wie folgt aus:
PNR STATUS DATUM ID
10 Vorbereitung 13:10:00 1
10 Nutzung 13:20:00 2

20 Deaktiviert 14:00:00 3
20 Deaktiviert 14:10:00 4
20 Deaktiviert 14:30:00 5

30 Deaktiviert 15:00:00 6
30 Vorbereitung 15:20:00 7

40 Deaktiviert 16:00:00 8


Die Abfrage soll folgendes erreichen:

a) wenn zu einem bestimmten PNR nur Elemente gehören, wo in der Spalte STATUS 'Deaktiviert' steht, dann muss das Query diejenige Zeile zurückgeben, wo das DATUM maximal ist.
D.H. für 20 soll es die Zeile zurückgeben, wo 14:30:00 steht.
Und für 40, die Zeile, wo 16:00:00 ist.

b) wenn zu einem PNR sowohl Deaktiviert, als auch etwas anderes in der Spalte STATUS steht, oder überhaupt kein Deaktiviert vorkommt, dann alle Zeilen, wo Deaktiviert nicht vorkommt.
D.h. für PNR=30 nur die Zeile, wo Vorbereitung steht, und für 10 die beide Zeilen.


Ich habe an einem stored procedure gedacht, wo ich änlich zum Java, alle PNR durchlaufe, und zu den jeweiligen PNR die Spalte STATUS abfrage, und entsprechende Werte aus der Spalte ID nehme. Dann kann ich die ursprüngliche Tabelle und die so zusammengestellten ID's miteinander verknüpfen.

Ginge es auch ohne proc?
(Wenn nicht oder zu kompliziert, dann ist es auch ok mit einem proc.)

Vielen Dank,
Daniel

 

[Tobse]

Ich denke das geht. Steht in der Tabelle wirklich "Vorbereitung", "Nutzung" und "Deaktiviert" oder ist das ein ENUM?

 

[Tobse]

Hab ein bisschen rumprobiert, das kam dabei raus:

wenn man die status spalte als ENUM (also als Zahl) darstellt, kann man sich die SUM() Funktion und ein GROUP-BY zu Nutze machen. In meine Fall 0 = Deaktiviert, 1 = Vorbereitung, 2 = Nutzung.

Wenn bei dir wirklich die Strings in der DB stehen musst du eine View drüber legen, welche dir die Strings durch Zahlen ersetzt; an sonsten kommst du mit meiner Lösung nicht weit.

Beim ersten Teil ist ein ziemlich schräger self-join notwendig gewesen weil sich nicht beeinflussen lässt, welche ID bei einem GROUP-BY zurückgegeben wird. Dadurch wird vorausgesetzt, dass ID unique ist und dass es ein DATUM pro PNR nur einmal gibt (sprich UNIQUE-Index auf PNR und DATUM).

Der zweite Teil ist vergleichsweise simpel, weil hier ja alle Zielen benötigt werden und nicht nur eine spezielle. Der Subquery ist trotzdem nötig, damit auch wirklich alle Zeilen dabei rauskommen.

SELECT
    test.pnr as pnr,
    test.`status` as `status`,
    temp_test.maxD as datum,
    (SELECT id FROM test WHERE pnr = temp_test.pnr AND datum = temp_test.maxD) AS id
FROM test
JOIN
    (SELECT test.*, MAX(datum) as maxD FROM test GROUP BY test.pnr HAVING SUM(`status`) = 0) temp_test
    ON temp_test.id = test.id
   
UNION

SELECT * FROM test WHERE pnr IN (SELECT pnr FROM test GROUP BY test.pnr HAVING SUM(`status`) != 0) AND `status` != 0

 

[deemon84]

JOIN
    (SELECT test.*, MAX(datum) as maxD FROM test GROUP BY test.pnr HAVING SUM(`status`) = 0) temp_test
    ON temp_test.id = test.id

Hallo,

vielen Dank für die super schnelle Antwort. Die Werte in der Spalte Status sind Strings, aber ich werde das View anpassen, damit Zahlen zurückgegeben werden.
Das einzige Problem hatte ich beim SELECT test.*. Dadurch werden auch Spalten ausgewählt, die nicht im GROUP BY vorkommen, und das ging nicht. Habe PNR und Datum als Schlüssel genommen.
Ansonsten hat es prima funktionert.

Vielen Dank,
Daniel

 

[Tobse]

Dadurch werden auch Spalten ausgewählt, die nicht im GROUP BY vorkommen, und das ging nicht. Habe PNR und Datum als Schlüssel genommen.
Ansonsten hat es prima funktionert.

Vielen Dank,
Daniel

Stimmt, das hatte ich nicht bedacht; auf meinem MySQL hat es funktioniert.

Keine Ursache

 

[Thallius]

Select * macht man ja auch nicht....