Fußball Blitztabelle

C

crz123

Benutzer
Beiträge
8
Hallo,
ich stehe gerade vor einer großen Kopfnuss - ich hoffe hier kann mir jemand weiterhelfen.

Ich möchte gern mit meinen Fußballergebnissen eine Blitztabelle darstellen.

Folgende Datenstruktur ist vorhanden:

Tabelle Spiele
spiele_id | datum | tore_heim | tore_gast | mannschaft_heim | mannschaft_gast | beendet
33236 | 2015-08-14T20:30:00 | 5 | 0 | 74 | 103 | 1
33237 | 2015-08-15T15:30:00 | 2 | 2 | 23 | 71 | 1

Tabelle Verein
verein_id | name | liga_id
74 | Hannover 96 | 1
103 | Dortmund | 1
71 | Bielefeld | 2
23 | Köln | 2

Wie bekomme ich es hin, dass aus den verschiedenen Ligen eine Blitztabelle entsteht mit folgenden Werten:

- Platz, Name, Spiele, Siege, Unentschieden, Niederlagen, Tore, Differenz, Punkte

Folgende Ansätze habe ich, jedoch möchte ich die gerne zusammenführen und nicht alles einzeln machen.


SELECT SUM(totalGoals) Tore
FROM
(
select sum(tore_heim) totalGoals from spiele WHERE beendet = 1 AND mannschaft_heim = 74
UNION ALL
select sum(tore_gast) totalGoals from spiele WHERE beendet = 1 AND mannschaft_gast = 74
) s;


SELECT SUM(againstGoals) Gegentore
FROM
(
select sum(tore_gast) againstGoals from spiele WHERE beendet = 1 AND mannschaft_heim = 74
UNION ALL
select sum(tore_heim) againstGoals from spiele WHERE beendet = 1 AND mannschaft_gast = 74
) s;


select mannschaft_heim, mannschaft_gast, tore_heim, tore_gast,
if((tore_heim > tore_gast),1,0) AS sieg_heim,
if((tore_heim = tore_gast),1,0) AS remis,
if((tore_heim < tore_gast),1,0) AS sieg_gast
from spiele
where beendet = 1 AND (mannschaft_heim = 74 OR mannschaft_gast = 74);

Jemand eine Idee?

Vielen Dank!
 
Werbung:
Hier eine MSSQL-Lösung:

Code: 
SELECT    v.liga_id AS Liga,
        RANK() OVER (PARTITION BY v.liga_id ORDER BY t.Punkte) AS Rang,
        v.name AS Verein,
        t.Spiele,
        t.Siege,
        t.Unentschieden,
        t.Niederlagen,
        t.Tore,
        t.Differenz,
        t.Punkte
FROM    (

SELECT    mannschaft_heim,
        count(*) AS Spiele,
        sum(CASE WHEN tore_heim > tore_gast THEN 1 ELSE 0 END) AS Siege,
        sum(CASE WHEN tore_heim = tore_gast THEN 1 ELSE 0 END) AS Unentschieden,
        sum(CASE WHEN tore_heim < tore_gast THEN 1 ELSE 0 END) AS Niederlagen,
        sum(tore_heim) AS Tore,
        sum(tore_heim) - sum(tore_gast) AS Differenz,
        sum(CASE WHEN tore_heim > tore_gast THEN 3 WHEN tore_heim = tore_gast THEN 1 ELSE 0 END) AS Punkte
FROM    (

SELECT    tore_heim,
        tore_gast,
        mannschaft_heim,
        mannschaft_gast
FROM    Spiele
UNION ALL
SELECT    tore_gast AS tore_heim,
        tore_heim AS tore_gast,
        mannschaft_gast AS mannschaft_heim,
        mannschaft_heim AS mannschaft_gast
FROM    Spiele

        ) s
GROUP BY mannschaft_heim

        ) t
LEFT JOIN Verein v
ON        t.mannschaft_heim = v.verein_id
ORDER BY 1,2
Bei RANK() OVER (PARTITION BY [...]) weiß ich nicht genau ob MySQL das kann. Wenn nicht musst du dir was basteln. Alles andere sollte aber auch MySQL können.
 
Hallo Ukulele,
vielen Dank für die Mühe und den Code!

In wie weit muss ich den oberen Code anpassen, dass es für MySQL funktioniert?

Code: 
RANK() OVER (PARTITION BY v.liga_id ORDER BY t.Punkte) AS Rang,

Vielen lieben Dank nochmal!
 
Angepasst habe ich es bereits und funktionieren tut es auch soweit so gut (vielen Dank nochmal dafür)

Jetzt ist es nur so, dass der Rang falschherum ist - sprich die reale 1 ist jetzt z.B. der letzte Wert z.B. die 18

Die Tabelle habe ich bereits nach Ihrem Status beendet gefiltert, um eine reale Tabelle zu erhalten (ansonsten wären die restlichen Termine die in der Zukunft stehen unentschieden).
Für eine Blitztabelle müsste ich doch jetzt nur noch das Datum prüfen oder?

WHERE beendet = 1 AND s.datum <= now () ?

Code: 
SELECT    v.liga_id AS Liga,
        t.Punkte AS Rang,
        v.name AS Verein,
        t.Spiele,
        t.Siege,
        t.Unentschieden,
        t.Niederlagen,
        t.Tore,
        t.Differenz,
        t.Punkte
FROM    (

SELECT    mannschaft_heim,
        count(*) AS Spiele,
        sum(CASE WHEN tore_heim > tore_gast THEN 1 ELSE 0 END) AS Siege,
        sum(CASE WHEN tore_heim = tore_gast THEN 1 ELSE 0 END) AS Unentschieden,
        sum(CASE WHEN tore_heim < tore_gast THEN 1 ELSE 0 END) AS Niederlagen,
        sum(tore_heim) AS Tore,
        sum(tore_heim) - sum(tore_gast) AS Differenz,
        sum(CASE WHEN tore_heim > tore_gast THEN 3 WHEN tore_heim = tore_gast THEN 1 ELSE 0 END) AS Punkte
FROM  (

SELECT  tore_heim,
        tore_gast,
        mannschaft_heim,
        mannschaft_gast
FROM    spiele
WHERE beendet = 1
UNION ALL
SELECT    tore_gast AS tore_heim,
        tore_heim AS tore_gast,
        mannschaft_gast AS mannschaft_heim,
        mannschaft_heim AS mannschaft_gast
FROM    spiele
WHERE beendet = 1
        ) s
GROUP BY mannschaft_heim
        ) t
LEFT JOIN verein v
ON        t.mannschaft_heim = v.verein_id
ORDER BY 1,2;
 
Also bei ORDER BY kannst du mit ASC und DESC bestimmen, wie der jeweilige Wert (hinter dem ASC oder DESC geschrieben wird) sortiert werden soll. ASC ist default, muss also nicht angegeben werden.

Ich denke mal beendet steht erst auf 1, wenn das Spiel auch zuende ist. Für eine Blitztabelle musst du also beendet = 1 OR datum <= now() nehmen.
 
Hallo,
ich bin immer noch am kniffeln - vllt hast Du ja noch einen Tipp.

Die Tabelle sieht an sich schon sehr gut aus - jetzt gibt es nur 2x Kleinigkeiten.

1. Er sortiert noch falsch, da er die Differenz nicht beachtet. Kann man dazu noch einen weiteren ORDER BY einbauen?
2. In der 2. Bundesliga hat sich Sandhausen -3 Punkte eingefangen, kann man das mit einem CASE eventuell nachbilden?

Code: 
SELECT  
                    v.liga_id AS Liga,
                    t.Punkte AS Rang,
                    v.name AS Verein,
                    t.Spiele,
                    t.Siege,
                    t.Unentschieden,
                    t.Niederlagen,
                    t.Tore,
                    t.Differenz,
                    t.Punkte
            FROM    (

            SELECT    mannschaft_heim,
                    count(*) AS Spiele,
                    sum(CASE WHEN tore_heim > tore_gast THEN 1 ELSE 0 END) AS Siege,
                    sum(CASE WHEN tore_heim = tore_gast THEN 1 ELSE 0 END) AS Unentschieden,
                    sum(CASE WHEN tore_heim < tore_gast THEN 1 ELSE 0 END) AS Niederlagen,
                    sum(tore_heim) AS Tore,
                    sum(tore_heim) - sum(tore_gast) AS Differenz,
                    sum(CASE WHEN tore_heim > tore_gast THEN 3 WHEN tore_heim = tore_gast THEN 1 ELSE 0 END) AS Punkte
            FROM  (
            SELECT    tore_heim,
                    tore_gast,
                    mannschaft_heim,
                    mannschaft_gast
            FROM    spiele
            WHERE beendet = 1 OR datum <= now()
            UNION ALL
            SELECT    tore_gast AS tore_heim,
                    tore_heim AS tore_gast,
                    mannschaft_gast AS mannschaft_heim,
                    mannschaft_heim AS mannschaft_gast
            FROM    spiele
            WHERE beendet = 1 OR datum <= now()
                    ) s
            GROUP BY mannschaft_heim
                    ) t
            LEFT JOIN verein v
            ON        t.mannschaft_heim = v.verein_id
            ORDER BY 1,2 DESC;

Bild zur Tabelle:
Bilder-Upload - Kostenlos Fotos hochladen und ins Netz stellen

Vielen Dank!
 
Du brauchst nur ein ORDER BY, alle Spalten nach denen sortiert werden soll werden in der entsprechenden Reheinfolge angegeben. Also z.B. v.liga_id, t.Punkte DESC, t.Differenz, t.Tore, etc.

Wenn es sich bei den -3 Punkten um Strafpunkte handelt solltest du das schon sauber im Tabellenmodell abbilden und nicht ins Select einbauen.
 
Hallo Ukulele,
vielen Dank für Deinen Hinweiß

Bei den -3 Punkte handelt es sich um Strafpunkte, da die Berechnung der Blitztabelle an sich aber über die Tabelle Spiele läuft, fehlt mir gerade der Ansatz zum Einbau der Strafpunkte im Tabellenmodell.
Würde jetzt ungern eine weitere Tabelle für diesen einen Fall anlegen. Hast Du da vllt. einen praktischen Tipp oder Ansatz für?

Tabelle Spiele
spiele_id | datum | tore_heim | tore_gast | mannschaft_heim | mannschaft_gast | beendet
33236 | 2015-08-14T20:30:00 | 5 | 0 | 74 | 103 | 1
33237 | 2015-08-15T15:30:00 | 2 | 2 | 23 | 71 | 1
 
Also theoretisch ginge das in der Tabelle Verein mit einer Spalte Strafpunkte. Das würde die Strafpunkte allerdings für jede Session berücksichtigen. Wie unterscheidest du denn überhaupt eine neue Session? Derzeit liegen der Wertung alle abgeschlossenen Spiele zugrunde und der Verein ist genau einer Liga zugeordnet, was sich auch ändern kann.
 
Es wird keine Historie geben - sofern die Saison vorbei ist sind die Spieldaten nicht mehr vorhanden. Das selbe geschieht auch beim Verein.

Das ganze läuft automatisiert über eine Schnittstelle ab.
 
Dann wäre es einfach: Die Tabelle Verein bekommt eine Spalte Strafpunkte und im äußersten Select ergänzt du t.Punkte - isnull(v.Strafpunkte,0) AS Punkte. Die Funktion zum Abfangen von NULL-Werten heißt glaube ich bei MySQL anders.
 
Werbung:
Ich habe der Tabelle Spiele jetzt eine Spalte mit Strafpunkten gegeben und bei Sandhauen 3 eingetragen, der Rest ist default 0.

Jetzt hatte ich mir gedacht, dass das doch so keine Abfrage mehr benötigt (function isnull) sondern nur noch minus gerechnet werden muss.
Das funktioniert auch soweit, nur ändert sich leider die Platzierung nicht von Sandhausen :-(

Habe ich im falschen SELECT gearbeitet?

Code: 
SELECT   
                    v.liga_id AS Liga,
                    t.Punkte AS Rang,
                    v.name AS Verein,
                    t.Spiele,
                    t.Siege,
                    t.Unentschieden,
                    t.Niederlagen,
                    t.Tore,
                    t.Differenz,
                    t.Punkte - v.Strafpunkte
            FROM    (

            SELECT    mannschaft_heim,
                    count(*) AS Spiele,
                    sum(CASE WHEN tore_heim > tore_gast THEN 1 ELSE 0 END) AS Siege,
                    sum(CASE WHEN tore_heim = tore_gast THEN 1 ELSE 0 END) AS Unentschieden,
                    sum(CASE WHEN tore_heim < tore_gast THEN 1 ELSE 0 END) AS Niederlagen,
                    sum(tore_heim) AS Tore,
                    sum(tore_heim) - sum(tore_gast) AS Differenz,
                    sum(CASE WHEN tore_heim > tore_gast THEN 3 WHEN tore_heim = tore_gast THEN 1 ELSE 0 END) AS Punkte
            FROM  (
            SELECT    tore_heim,
                    tore_gast,
                    mannschaft_heim,
                    mannschaft_gast
            FROM    spiele
            WHERE beendet = 1 OR datum <= now()
            UNION ALL
            SELECT    tore_gast AS tore_heim,
                    tore_heim AS tore_gast,
                    mannschaft_gast AS mannschaft_heim,
                    mannschaft_heim AS mannschaft_gast
            FROM    spiele
            WHERE beendet = 1 OR datum <= now()
                    ) s
            GROUP BY mannschaft_heim
                    ) t
            LEFT JOIN verein v
            ON        t.mannschaft_heim = v.verein_id
            ORDER BY v.liga_id, t.Punkte DESC, t.Differenz DESC, t.Tore DESC;
 

Ähnliche Themen

T
Summe von Zeitdifferenzen
2
Antworten
21
Aufrufe
275
tsunami
T
H
Fussball (Tabellenplatzierung)
Antworten
3
Aufrufe
442
Heinzi79
H
H
Auf und Abstieg oder Position gehalten einbauen
2
Antworten
23
Aufrufe
1K
ukulele
ukulele
O
Abfrage mit Kombinationen
Antworten
7
Aufrufe
161
ukulele
ukulele
M
Subselect der einen Wert zurück gibt
Antworten
2
Aufrufe
144
MScalli
M
Zurück
Oben