Zeiten für Bestenliste abfragen
Tobias Kloth
0 0 Tobias Kloth
0
0 0
Zeiten für Bestenliste abfragen
Tobias Kloth
Hallo zusammen,
Ich versuche gerade die Daten für eine Bestenliste aus einer Datenbank (MySql4.0.24) zu holen, dabei gibt es zwei relevante Tabellen, zum einen die Tabelle "mitglieder" mit den Spalten id, vorname, nachname, mw* und gebdatum und zum anderen die Tabelle "zeiten" mit den Spalten mitgliedid, datum (DATE), zeit (TIME), hundertstel (tinyint(4))**, disziplin und streckenlaenge (die unwichtigen Spalten habe ich mal weggelassen).
* speichert ob das Mitglied männlich oder weiblich ist
** da ich ja keine Hundertstel Sekunden in einer Spalte im TIME-Format speichern kann, eben eine extra Spalte
Jetzt möchte ich jeweils für die 10 besten Zeiten zu einer Disziplin, einer Streckenlänge und einem Geschlecht - und natürlich darf nur jedes Mitglied nur einmal in der Liste auftauchen, auch wenn es mehrere Zeiten unter den Besten 10 hat.
Versucht habe ich das mit den Query:
SELECT
m.nachname as name,
m.vorname,
DATE_FORMAT(m.gebdatum,'%Y') as jg,
CONCAT(TIME_FORMAT(z.zeit,'%i:%s'),',',LPAD(z.hundertstel,2,'0')) as zeit,
DATE_FORMAT(z.datum,'%d.%m.%Y') as datum
FROM
mitglieder as m,
zeiten as z
WHERE
m.id=z.mitgliedid AND
m.mw = 'm' AND
z.disziplin = 4 AND
z.streckenlaenge = 100
ORDER BY
z.zeit,
z.hundertstel,
z.datum DESC
LIMIT 10
Die Zeiten die ich damit bekomme stimmen auch - nur leider werden Mitglieder mehrfach ausgegeben. Wenn ich vor ORDER BY noch ein GROUP BY m.id setze, bekomme ich zwar jedes Mitglied nur einmal, aber leider die falschen Zeiten.
Hat vielleicht jemand einen Schubser in die richtige Richtung, wie ich das Problem (am besten mit nur einem Query) lösen kann?
Grüße aus Nürnberg
Tobias
-
Hallo Tobias
Iljas Beitrag in folgendem Archivthread könnte Dir weiterhelfen, wenn Deine MySQL-Version >= 4.1 wäre :-(
Ich wüsste leider keine einfache Lösung mit Deiner MySQL-Version, die _alle_ von Dir gewünschten Spalten zurückliefert. Die richtigen Zeiten solltest Du zwar mit MIN() bekommen können, aber wie Du an das Datum der Bestleistung kommst - ohne ein korrelierendes Subselect (MySQL 4.1) oder den Einsatz eines View (MySQL 5.x) weiß ich leider nicht.
Freundliche Grüße
Vinzenz
PS: Wäre nicht noch der Ort bzw. die Veranstaltung, bei der die Bestleistung aufgestellt wurde, von Interesse?
-
Hallo Vinzenz,
Iljas Beitrag in folgendem Archivthread könnte Dir weiterhelfen, wenn Deine MySQL-Version >= 4.1 wäre :-(
Hier bei mir könnte ich die Version ja installieren - nur beim Provider ist es eben immernoch eine 4.0er ...
Ich wüsste leider keine einfache Lösung mit Deiner MySQL-Version, die _alle_ von Dir gewünschten Spalten zurückliefert. Die richtigen Zeiten solltest Du zwar mit MIN() bekommen können, aber wie Du an das Datum der Bestleistung kommst - ohne ein korrelierendes Subselect (MySQL 4.1) oder den Einsatz eines View (MySQL 5.x) weiß ich leider nicht.
ja, mit MIN hatte ich auch schon probiert aber leider ohne Erfolg - das Problem ist jedes Mitglied nur einmal auf der Liste erscheinen zu lassen. Ich hoffe einfach mal, dass Iljas oder Daniela (oder jemand anderes) noch eine Idee haben, ansonsten musse ich es wohl etwas umständlicher per Script machen.
PS: Wäre nicht noch der Ort bzw. die Veranstaltung, bei der die Bestleistung aufgestellt wurde, von Interesse?
prinzipiell schon - aber da die Daten bevor ich sie übernommen habe, ohne Verknüpfung zum zugehörigen Wettkampf gespeichert wurden, geht das nicht (jetzt wäre es kein Problem, in der Zeiten-Tabelle gibt es noch eine Spalte "wettkampfid") - und so ist unsere "Hall of Fame" eben ohne Veranstalltungsort :-)
Grüße aus Nürnberg
Tobias-
Hallo Tobias
ja, mit MIN hatte ich auch schon probiert aber leider ohne Erfolg - das Problem ist jedes Mitglied nur einmal auf der Liste erscheinen zu lassen. Ich hoffe einfach mal, dass Iljas oder Daniela (oder jemand anderes) noch eine Idee haben, ansonsten musse ich es wohl etwas umständlicher per Script machen.
Was liefert folgendes ungetestetes Statement?
SELECT m.nachname as name, m.vorname, DATE_FORMAT(m.gebdatum,'%Y') as jg, MIN(CONCAT(TIME_FORMAT(z.zeit,'%i:%s'),',',LPAD(z.hundertstel,2,'0'))) as zeit FROM mitglieder AS m INNER JOIN zeiten AS z ON m.id=z.mitgliedid GROUP BY name, vorname, jg WHERE m.mw = 'm' AND z.disziplin = 4 AND z.streckenlaenge = 100 ORDER BY zeit LIMIT 10Freundliche Grüße
Vinzenz
-
Hallo Vinzenz,
Was liefert folgendes ungetestetes Statement?
Du bist ein Schatz :-) Nach dem Umdrehen der Reihenfolge von GROUP BY und WHERE (WHERE gehört vor GROUP BY) liefert das genau das Ergebnis das ich brauche! Danke!
SELECT [...] FROM mitglieder AS m
INNER JOIN zeiten AS z ON m.id=z.mitgliedid[...]
mit JOIN hatte ich auch schon experiementiert, aber nicht mit einem INNER JOIN - zumal mir die JOINs sowieso etwas suspekt sind :-) Grüße aus Nürnberg Tobias -
Hallo nochmal,
Was liefert folgendes ungetestetes Statement?
*grmpf* zu früh gefreut: das Query funktioniert zwar wunderbar - ich brauche aber noch das zugehörige Datum und das funktioniert nicht mehr. Wenn ich einfach bei den zu selektierenden Spalten das Datum hinzufüge, wird irgend ein Datum ausgegeben, aber nicht das zum Datensatz gehörige - wenn ich dann bei GROUP BY testhalber noch die Datumsspalte hinschreibe bekomme ich die gleichen Mitglieder wieder mehrfach - hat noch jemand eine Idee?
Grüße aus Nürnberg
Tobias-
Hallo Tobias
*grmpf* zu früh gefreut: das Query funktioniert zwar wunderbar - ich brauche aber noch das zugehörige Datum und das funktioniert nicht mehr.
Genau das ist das Problem mit Deiner MySQL-Version, siehe https://forum.selfhtml.org/?t=108952&m=679818. Ich schrieb nicht umsonst:
»»»» Ich wüsste leider keine einfache Lösung mit Deiner MySQL-Version, die _alle_ von Dir gewünschten Spalten zurückliefert.
Beachte bitte: "_alle_".
Du musst die Aggregatsfunktion MIN() bemühen, also gruppieren. Leider lassen sich nur Nachname, Vorname und Jahrgang sinnvoll gruppieren. Das Datum kannst Du nicht sinnvoll gruppieren, infolgedessen benötigst Du z.B. eine korrelierendes SELECT-Statement, das geht aber erst ab 4.1.
Ich sehe derzeit keinen Weg, Dein Ziel mit Deiner MySQL-Version zu erreichen. Natürlich lasse ich mich auch gern eines Besseren belehren.
Freundliche Grüße
Vinzenz
-
-
-
-
-
hi,
die Tabelle "zeiten" mit den Spalten mitgliedid, datum (DATE), zeit (TIME), hundertstel (tinyint(4))**
** da ich ja keine Hundertstel Sekunden in einer Spalte im TIME-Format speichern kann, eben eine extra Spalte
d.h., der wert von zeit und hunderstel müssen erst zusammengefügt werden, damit sie das ergebnis bilden?
da würde ich aber doch eher zu einem INT greifen, in dem dann die gesamtzeit in hunderstel sekunden umgerechnet drinsteht, oder alternativ vielleicht auch ein DECIMAL mit sekunden komma hunderstel.
deine derzeitige lösung mag die "bequemste" sein, um den wert "optisch schön" darzustellen - als HH:MM:SS,xy - aber diese aufteilung _eines_ datums auf _zwei_ spalten findet vermutlich jeder, der sich ein bisschen mit datenmodellierung beschäftigt hat, ziemlich furchtbar.
gruß,
wahsaga--
/voodoo.css:
#GeorgeWBush { position:absolute; bottom:-6ft; }-
Hallo wahsaga,
** da ich ja keine Hundertstel Sekunden in einer Spalte im TIME-Format speichern kann, eben eine extra Spalte
d.h., der wert von zeit und hunderstel müssen erst zusammengefügt werden, damit sie das ergebnis bilden?ja.
deine derzeitige lösung mag die "bequemste" sein, um den wert "optisch schön" darzustellen - als HH:MM:SS,xy - aber diese aufteilung _eines_ datums auf _zwei_ spalten findet vermutlich jeder, der sich ein bisschen mit datenmodellierung beschäftigt hat, ziemlich furchtbar.
Ich weiß, mir gefällt das auch nicht wirlich gut, aber beim Erzeugen des Formates MM:SS,hh (Stunden brauche ich nicht, es handelt sich um Schwimmzeiten über meist 100m) aus einem INT werde ich auch nicht wirklich glücklich:
CONCAT( LPAD(ROUND((FLOOR(zeit/100)-(zeit/100)%60)/60),2,'0'), ':', LPAD((zeit/100)%60,2,'0'), ',', LPAD(zeit-FLOOR(zeit/100)*100,2,'0') ) as zeitdie jetzige Variante:
CONCAT( TIME_FORMAT(z.zeit,'%i:%s'), ',', LPAD(z.hundertstel,2,'0') )ist dagegen doch wesenlich einfacher (auch wenn man das oben vielleicht noch einfacher machen könnte) - aber an meinem ursprünglichen Problem ändert das auch nichts :-)
Grüße aus Nürnberg
Tobias
-
-
Hallo Tobias
http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html zeigt einen nicht effizienten Trick, dort MAX-CONCAT-Trick genannt. Du müsstest den MIN-CONCAT-Trick verwenden :-(
Freundliche Grüße
Vinzenz
-
Hallo Vinzenz,
http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html zeigt einen nicht effizienten Trick, dort MAX-CONCAT-Trick genannt. Du müsstest den MIN-CONCAT-Trick verwenden :-(
Danke für den Tipp - damit funktioniert es, auch wenn der Query jetzt wirklich _sehr_ schmutzig ist (Datenbankexperten bitte hier nicht mehr weiterlesen ...):
SELECT LEFT(MIN(CONCAT( TIME_FORMAT(z.zeit,'%i:%s'), ',', LPAD(z.hundertstel,2,'0'), DATE_FORMAT(z.datum,'%d.%m.%Y'), DATE_FORMAT(m.gebdatum,'%Y'), LPAD(m.nachname,50,' '), LPAD(m.vorname,30,' ') )),8) as zeit, SUBSTRING(MIN(CONCAT( TIME_FORMAT(z.zeit,'%i:%s'), ',', LPAD(z.hundertstel,2,'0'), DATE_FORMAT(z.datum,'%d.%m.%Y'), DATE_FORMAT(m.gebdatum,'%Y'), LPAD(m.nachname,50,' '), LPAD(m.vorname,30,' ') )),9,10) as datum, SUBSTRING(MIN(CONCAT( TIME_FORMAT(z.zeit,'%i:%s'), ',', LPAD(z.hundertstel,2,'0'), DATE_FORMAT(z.datum,'%d.%m.%Y'), DATE_FORMAT(m.gebdatum,'%Y'), LPAD(m.nachname,50,' '), LPAD(m.vorname,30,' ') )),19,4) as jahrgang, TRIM(SUBSTRING(MIN(CONCAT( TIME_FORMAT(z.zeit,'%i:%s'), ',', LPAD(z.hundertstel,2,'0'), DATE_FORMAT(z.datum,'%d.%m.%Y'), DATE_FORMAT(m.gebdatum,'%Y'), LPAD(m.nachname,50,' '), LPAD(m.vorname,30,' ') )),23,50)) as name, TRIM(SUBSTRING(MIN(CONCAT( TIME_FORMAT(z.zeit,'%i:%s'), ',', LPAD(z.hundertstel,2,'0'), DATE_FORMAT(z.datum,'%d.%m.%Y'), DATE_FORMAT(m.gebdatum,'%Y'), LPAD(m.nachname,50,' '), LPAD(m.vorname,30,' ') )),73,30)) as vorname FROM mitglieder AS m, zeiten AS z WHERE m.mw = 'm' AND z.disziplin = 4 AND z.streckenlaenge = 100 AND m.id = z.mitgliedid GROUP BY m.id ORDER BY zeitWerde ich jetzt es erstmal lassen - auf Geschwindigkeit kommt es nicht wirklich an und früher oder später wird die Seite sowieso wieder überarbeitet.
Grüße aus Nürnberg
Tobias
-