SQL-Abfragen addieren für mehrere Einträge

[invention]

Guten Tag liebe Menschen!

Ich stehe vor einem kleinen Problem und benötige mal kompetente Hilfe, da die Aufgabenstellung in meinen Augen etwas tiefgreifendere Kenntnisse erfordert als ich besitze.

Die Grundlage:
Eine Tabelle mit Mannschaften und eine Tabelle mit Spielen.

Ziel:
Über eine Abfrage eine Tabelle erzeugen, die zu jeder Mannschaft die aus den Spielen erlangten Punkte (Sieg 3 Punkte, Unentschieden 1 Punkt) auflistet und alles absteigend nach Punkten sortiert.

Der Tabellen-Aufbau ist eigentlich frei wählbar. Ich habe nun mit einer Mannschafts und einer Spieltabelle begonnen, da ich Redundanzen vermeiden möchte und alles sehr schmal halten möchte.
Darauf aufbauend habe ich mir folgende Struktur überlegt:

mannschaft:
id (INT | PRIMARY | AI)
mname (VARCHAR)

spiele:
id (INT | PRIMARY | AI)
spieltag (ID)
mannschaft_heim (INT)
tore_heim (INT)
mannschaft_gast (INT)
tore_gast (INT)

Dieser Aufbau müsste meiner Meinung nach alle nötigen Daten enthalten.

Die Frage ist nur jetzt: Wie zur Hölle kriege ich das hin, dass jede Mannschaft mit Ihren erlangten Punkten ausgegeben wird???

Was ich bereits habe:
SELECT wins.wpunkte+draws.dpunkte AS gespunkte FROM (SELECT COUNT(id)*3 AS wpunkte FROM spiele WHERE (mannschaft_heim = 'MANNSCHAFTSID' AND tore_heim > tore_gast) OR (mannschaft_gast = 'MANNSCHAFTSID' AND tore_heim < tore_gast)) AS wins, (SELECT COUNT(id) AS dpunkte FROM spiele WHERE (tore_heim = tore_gast) AND (mannschaft_gast = 'MANNSCHAFTSID' OR mannschaft_gast = 'MANNSCHAFTSID')) AS draws

Das berechnet mir schon mal die Punkte eines Teams, wenn ich die MANNSCHAFTSID einsetze. Aber wie mache ich das dynamisch? Stehe dort gerade echt auf dem Schlauch...

Kann mir jemand von euch Profis helfen?

Viele Grüße

 

[akretschmer]

Das berechnet mir schon mal die Punkte eines Teams, wenn ich die MANNSCHAFTSID einsetze. Aber wie mache ich das dynamisch? Stehe dort gerade echt auf dem Schlauch...

test=*# select * from mannschaft ;
 id |  name
----+--------
  1 | team 1
  2 | team 2
  3 | team 3
  4 | team 4
(4 rows)

Time: 0,289 ms
test=*# select * from spiele ;
 id | heim | gast | tore_heim | tore_gast
----+------+------+-----------+-----------
  1 |  1 |  2 |  2 |  3
  2 |  1 |  3 |  3 |  1
  3 |  1 |  4 |  4 |  0
  4 |  2 |  4 |  3 |  3
  5 |  3 |  4 |  1 |  5
(5 rows)

Time: 0,173 ms
test=*# select team, sum(punkte) from (select heim as team, case when tore_heim > tore_gast then 3 when tore_heim = tore_gast then 1 else 0 end as punkte from spiele  union all select gast, case when tore_gast > tore_heim then 3 when tore_gast = tore_heim then 1 else 0end from spiele) bla group by team;
 team | sum
------+-----
  4 |  4
  1 |  6
  3 |  0
  2 |  4
(4 rows)

Das gegen die mannschaft zu JOINen, um den Namen der Mannschaft rauszubekommen überlasse ich Dir zur Übung.

 

[invention]

Wow, starke Sache!

Du hast mir echt das berühmte Hinterteil gerettet, vielen Danke!!