SQL Sortierung von "Group By" nicht möglich

[Peter.xD]

Hallo zusammen,

ich habe ein Problem zu der Sortierung von GROUP BY abfragen.

Die Ausgangssituation ist, dass ich zwei Tabellen für die Verwaltung von Ausbildungen habe. In meiner ersten Datenbank sind die angaben zu der Ausbildung hinterlegt(ID, Name, Laufzeit). In der zweiten Tabelle sind die Daten hinterlegt, wann ein User die Ausbildung gemacht hat und welche er gemacht hat(ID, Ausbildungs_ID, ZEIT, USER).

Ich verknüpfe nun die Tabellen, sodass ich einen Überblick habe, welche Ausbildungen von welchem User als nächstes erneuert werden müssen.

$sort = mysql_query("SELECT a.id aid, a.laufzeit, e.ausbildungs_id, e.zeit ezeit, e.id eid FROM ausbildungen_allgemein a, ausbildungen_erneuern e WHERE a.id = e.ausbildungs_id GROUP BY aid, e.user ORDER BY e.zeit DESC");

Nun folgt eine Zusammenführung der Zeit und der Laufzeit durch addieren in php.

Wenn ich mir das Ergebnis nun anzeigen lasse, zeigt mir das script aber immer die erste Durchführung der Ausbildung des nutzers an, welche er gemacht hat, jedoch nicht die letzte, welche ich benötige.

Als Beispiel:

ID	Name	Laufzeit
1	Ausbildung 1	5
2	Ausbildung 2	2

Und

ID	Ausbildungs_id	Zeit	User
1	1	1277935200	1
2	2	1277935200	2
3	1	1446593242	1
4	1	1446593242	2

Dann ist das Momentane Ergebnis:

Ausbildung	User	Zeit
Ausbildung 2	2	1277935200
Ausbildung 1	1	1277935200
Ausbildung 1	2	1446593242

Anzeigen soll es aber

Ausbildung	User	Zeit
Ausbildung 1	1	1446593242
Ausbildung 1	2	1446593242
Ausbildung 2	2	1277935200

Ich hatte schon versucht, es mit einem subquery zu lösen, allerdings bin ich immer gescheitert, da nur Fehlermeldungen ausgegeben wurden.
Ein versuch mit MAX(e.zeit) half nichs, da hier nur noch gesamt 1 Eintrag für alle User ausgegeben wurde.

Ich hoffe, dass mir von euch jemand helfen kann.

 

[amokkx]

Hm, spontan würde ich dein jetziges SQL als Tabelle nehmen und ein weiteres SQL dann zum selecten und ordern.

select ausbildung,user,zeit from (<dein jetziges sql>) order by ausbildug desc

EDIT: Sorry, Freitags-Modus, hab da was vertauscht. Aber sollte dennoch klappen wenn man das SQL dementsprechend abändert xD

 

[nittels]

SELECT a.Name, e."user", max(e.zeit) ezeit 
FROM ausbildungen_allgemein a
JOIN ausbildungen_erneuern e
ON a.id = e.ausbildungs_id
GROUP BY a.Name, e."user"
ORDER BY e."user", ezeit DESC

Name	user	ezeit
Ausbildung 1	1	1446593242
Ausbildung 1	2	1446593242
Ausbildung 2	2	1277935200

Kann sein dass du die Syntax etwas abändern musst; hab das schnell am SQL Server runtergetippt, hab mich aber denk ich soweit an den universalen SQL Standard gehalten dass MySQL das auch versteht. Möglich dass du aus dem JOIN ein INNER JOIN machen musst. Kenn den MySQL Dialekt nicht.

 

[Peter.xD]

@nittels:
Leider spuckt mir das Script, genauso wie bei dem subquery den Fehler aus.

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in ausbildungen.php on line 208

Ich glaube langsam sehe ich den Wald vor lauter Bäumen nicht mehr, da ich seit 2 Tagen am tüfteln bin.

Edit: Das mit dem normalem Join ist ein guter Ansatz, ich denke, hier kann ich weiter tüfteln und die Lösung finden
Danke vielmals

Edit2: Jetzt hab ichs geschafft, vielen Dank für die schnelle und sehr gute Hilfe

 

[mambokurt]

Irgendwie haben 50% der Probleme mit MySQL hier mit dessen laxen Umgang mit Group By und einem fundamentalem Verständnismangel davon zu tun, was Group By eigentlich macht.

Group By X heißt einfach nur: fassse zusammen soweit wie es geht nach unterschiedlichen X. Hast du jetzt eine Tabelle mit den Spalten X und Y, selektierst beide ganz normal und gruppierst nur nach X, was bekommst du als Ergebnis? Also:

X	Y
1	2
1	3

SELECT X, Y FROM bla GROUP BY X

Was kriegst du da raus? Die Antwort ist bei allen Datenbanken außer MySQL und SQLite pauschal eine Fehlermeldung, weil die Datenbank nicht weiß welches Y sie denn nun zurückgeben soll, 2 oder 3. MySQL und SQLite geben dir ein (AFAIR) zufälliges Y zurück, es kann also 1,2 als Ergebnis zurückkommen oder 1,3, je nachdem was er zuerst findet.

Das ist genau dein Problem, er fasst zusammen nach zwei Spalten, die Laufzeit bekommst du eher zufällig zurück, das ORDER BY wird dann auf die schon zusammengefassten Ergebnisse angewendet, da ist das Kind schon lange im Brunnen.

Zur Lösung deines Problems:
Wir holen einfach die maximale Zeit, die Ausbildungsid und den User aus der zweiten Tabelle und gruppieren nach User und AusbildungsId:

SELECT max(zeit), ausbildungs_id, user FROM ausbildungen_erneuern GROUP BY ausbildungs_id, user

So, da brauchen wir jetzt aber noch die Laufzeit, Ausbildungsnamen und Id, da die aber das eigentliche Ergebnis gar nicht groß tangieren müssten wir die einfach mit einem Left Join mit reinklatschen können, selbstverständlich müssen die dann mit ins Group By:

SELECT max(zeit) as zeit, ausbildungs_id, user, laufzeit, name, tab2.id AS id FROM ausbildungen_erneuern tab1 LEFT JOIN ausbildungen_allgemein tab2 ON tab1.ausbildungs_id = tab2.id  GROUP BY ausbildungs_id, user, laufzeit, name, tab2.id

So, und jetzt wo du grundsätzlich erstmal die richtigen Ergebnisse bekommen solltest, jetzt kannst du dir überlegen wie du die ganze Chose mit ORDER BY sortieren möchtest.

Ich hoffe mal das läuft so kanns ja schlecht testen

 

[Peter.xD]

Guten Abend,

nachdem das Problem mit zwei Tabellen so gut erkärt wurde, hoffe ich, dass ich nocheinmal auf eure Hilfe hoffen darf.

Ich versuche die Daten aus 3 Tabellen zusammen zu fassen. Es gibt keine Konstante, welche in allen 3 Tabellen vorhanden ist, sondern jede Tabelle besitzt nur eine Konstante zur jeweils übergeordneten.

Für die Wartung von Material habe ich die Tabelle mit dem Material(z.B. Einem Fahrzeug). Diese Tabelle(material) enthält "id", "name" und "ort". In diesem Fall gibt es in dem Fahrzeug verschiedene Inhalte. Diese haben die Tabelle "inhalt" bekommen. Diese Tabelle enthält "id", "materialid", "name" und "wartungsintervall"(in Monaten).
Zum Schluss habe ich noch die Tabelle, in welcher alle Wartungen erfasst sind. Diese Tabelle(wartung) enthält "id", "inhaltid" und "zeit"(als timestamp).

Jetzt ist die Frage, ob es überhaupt möglich ist, dies in einem einzelnden Join zusammenzufassen und entsprechend zu sortieren.
Die Liste soll nach Name des Materials sortiert sein, dahinter soll jeweils der Name des Inhalts stehen, der Lagerort und das Datum der letzten Wartung, welches mit dem Wartungsintervall addiert ist.

Bisher habe ich immer zwei Abfragen genutzt, doch hier ergibt sich mit den 3 Tabellen wieder das obige Problem, dass ich nicht so sortieren kann, wie ich es mir wünsche.

Bisher sieht es so aus:

$sort = mysql_query("SELECT m.name mname, m.id, i.id iid, i.wartungsintervall, i.materialid, w.zeit, w.inhaltid FROM material m, inhalt i, wartung w WHERE i.id = w.inhaltid AND m.id = i.materialid ORDER BY m.name");

und in der zweiten Abfrage:

$liste = mysql_query("SELECT m.name mname, m.ort, i.id iid, i.name iname FROM material m, inhalt i WHERE i.materialid = m.id AND $key = i.id");

zwischendrin habe ich ein array zum sortieren genutzt.

Als Beispiel:
Tabelle material

id	name	ort
1	Fahrzeug 1	Unterkunft 1
2	Fahrzeug 2	Unterkunft 2

Tabelle inhalt

id	materialid	name	wartungsintervall
1	1	Motoröl	24
2	1	Standheizung	4
3	2	Motoröl	24

Tabelle wartung

id	inhaltid	zeit
1	1	1277935200
2	1	1445551200
3	2	1443132000
4	3	1447542000

Ausgabe

Materialname	Inhaltname	Lagerort	Nächste Wartung
Fahrzeug 1	Motoröl	Unterkunft 1
Fahrzeug 1	Standheizung	Unterkunft 1
Fahrzeug 2	Motoröl	Unterkunft 2

 

[nittels]

Ich habs mal schnell runtergetippt, aber nicht getestet (Syntaxfehler vorbehalten ;-)

SELECT m.name as Materialname, i.name AS Inhaltname, m.ort AS LagerOrt, 
max(w.zeit) AS "Nächste Wartung"
FROM material AS m
INNER JOIN inhalt i ON m.id = i.materialid
INNER JOIN wartung w ON i.id = w.inhaltid
GROUP BY m.name, i.name, m.ort
ORDER BY m.name, "Nächste Wartung" DESC

Bei "addieren" der Zeit weiß ich nicht ganz was du meinst. Normalerweise hat man einen entsprechenden Datentyp. Wenns denn so ist muss man mit einer Date Function arbeiten, z.B. max(Dateadd("Month", 24, w.zeit)).

 

[mambokurt]

Bei "addieren" der Zeit weiß ich nicht ganz was du meinst. Normalerweise hat man einen entsprechenden Datentyp. Wenns denn so ist muss man mit einer Date Function arbeiten, z.B. max(Dateadd("Month", 24, w.zeit)).

Sieht aus wie ein normaler Unixtimestamp, dann wärs im Zweifelsfall w.zeit + intervall*30.4375*86400 (also Anzahl Monate im Intervall mal durchschnittliche Anzahl Tage im Monat * 86400 Sekunden in einem Tag). Da hat man natürlich nicht ganz genau 24 Monate, kann auch auch mal ein Tag mehr oder weniger sein, aber wenn man da dem Kunden eine Mail schicken will dass der Intervall rum ist oder selbst einen Auftrag auslöst oder sowas wird man da sowieso noch eine Woche (oder so) von abziehen und da den Brief schicken/Auftrag auslösen, dann schei** sich das weg.

 

[Peter.xD]

Hallo zusammen,

danke für die super Hilfe.

Mit dem Addieren habe ich gemeint, dass ich den wartungsintervall zu der Zeit der Wartung dazu zähle. Ich habe das nun einfach in einem weiterem Schritt gemacht:

$nextWartung = strtotime('+'.$wartungsintervall.' month', $LetzteWartung);

So klappt alles perfekt. Vielen vielen Dank