Etwas kompliziertere SQL Abfrage. Wer kann helfen?

[Civlo]

Hi,

ich arbeite zurzeit an einem Tippspiel zur WM. Ist alles online mit php und mySQL realisiert. Eine SQL Abfrage, die ich bisher benutzt habe ist nicht ganz fehlerfrei. Bei dieser Vorrunde allerdings gab es keinen Fall, in dem ich "manuell" nachhelfen musste, deshalb kein Problem, aber trotzdem interessiert mich natürlich die richtige Abfrage.

Folgendes Beispiel:

Ich habe eine Tabelle, nennen wir sie teams, in der jede Mannschaft mit folgenden Informationen drin steht (angezeigt sind nur die für die Abfrage relevanten):

teams
id    name    gf    ga    points
--------------------------------
 1     B      3     1      6
 2     A      3     1      6
 3     C      0     4      0
 4     D      2     2      6

Kurze Erläuterung: gf bedeutet goals for (erzielte Tore), ga bedeutet goals against (erhaltene Tore) und points sollte klar sein. Die Informationen wie gf, ga und points werden bei Eintrag eines Spielergebnisses geupdated. Sie dienen dazu um an manchen Stellen der Website schnellen Zugriff darauf zu haben.

Die Spiele sind in der nächsten Tabelle, games:

games
gameid    team1    team2    score1    score2
-----------------------------------------------
 1         2        1        1         0
 2         3        4        0         1
 3         2        3        2         0
 4         1        4        2         0
 5         2        4        0         1
 6         1        3        1         0

So, meine aktuelle Select Abfrage sieht wie folgt aus:

SELECT * ,  gf - ga 'diff' FROM teams ORDER BY points DESC, diff DESC, gf DESC

Die Abfrage gibt alle Zeilen aus, sortiert nach Punkten (absteigend), Tordifferenz (absteigend) und erzielten Toren (absteigend). Wie gesagt, bei der diesjährigen WM gab es auch keinen Fall in der Vorrunde, bei dem alle 3 Kriterien bei 2 Mannschaften erfüllt waren. Trotzdem würde ich das gerne korrekt hinbekommen.

Im Falle der obigen Tabellen, gibt es dann nämlich 2 Mannschaften, die sich den ersten Platz teilen.

Die Regeln laut Fifa für die Tabellenreihenfolge sind:

1. Punkte aus allen Begegnungen
2. Tordifferenz aus allen Begegnungen
3. erzielte Tore aus allen Begegnungen
4. Punkte aus Direktbegegnungen
5. Tordifferenz aus Direktbegegnungen
6. erzielte Tore aus Direktbegegnungen

Punkt 1 bis 3 habe ich mit meiner Abfrage abgedeckt. Ich könnte das natürlich versuchen mit PHP zu berechnen und zu ermitteln. Ich möchte nur wissen, ob es mit SQL vielleicht möglich ist. Ich vermute, dass die Abfrage dann nicht mehr mit der Tabelle teams erfolgt sondern zum größtenteil mit der Tabelle games. Doch von den ganzen Verknüpfungen zwischen Tabellen habe ich leider keine große Ahnung. Wenn mir hier jetzt gesagt wird, dass es NUR mit SQL nicht möglich ist, dann muss ich das mit PHP machen.

Die Tabelle muss zum Schluss wie folgt aussehen:

1. Team A (id 2)
2. Team B (id 1)
3. Team D (id 4)
4. Team C (id 3)

[Falk Prüfer]

Hallo,

da SQL mengenorientiert arbeitet und es dabei soetwas wie Vorgänger und Nachfolger nicht gibt, du dich für die Sortierung jedoch auf einen Vorgänger bzw. Nachfolger beziehen müßtest (um z.B. die Punkte aus dem Spiel gegeneinander zu ermitteln), wird das mit reinem SQL nicht gehen. Zumindest nicht ohne SPs.

Gruß Falk

[dimitri]

Mit analythischen Funktionen (die Teil des SQL Standards sind) geht das sehr wohl, allerdings weiß ich nicht, ob es diese in mysql schon implementiert wurden.

Dim