Würde mich über Denkanstoß sehr freuen!

[ViecFan]

Hallo Communitiy!
Ich benutze eine kleine PHP-Applikation um Daten für Eishockey zu erfassen und zu verarbeiten. Das betreffende Tabellenschema kommt nicht von mir und kann auch nicht angepasst werden, ohne die Funktionalität der APP negativ zu beeinflussen......

Tabelle: custom
Felder(nur die relevanten):
team1_id --> Fremdschlüssel in Teamtabelle für Heimteam
team2_id--> Fremdschlüssel in Teamtabelle für Auswärtsteam
score1 --> erzielte Tore Heimteam (team1)
score2--> erzielte Tore Auswärtsteam (team2)

Daraus ergibt sich die Logik, wenn
score1 == score2 - Spiel endet mit x:x -Toren unentschieden, jedes Team bekommt 1 Punkt für die Tabelle
score1 < score2 - Team2 gewinnt mit x:x Toren und bekommt 2 Punkte für die Tabelle
score1 > score2 - Team1 gewinnt mit x:x Toren und bekommt 2 Punkte für die Tabelle

gleichzeitig würde sich auch die Tordifferenz ergeben (Differenz erhaltene, erzielte Tore)

Problem:
in einem einzigen Query kann ich nicht nach den Teams gruppieren und die Summen der Tore bilden, weil ich einerseits aus der Logik den Sieger ermittle und beide ID's (team1 + team2) in die selbe Teamtabelle joinen, zumindest fehlt mir dazu der Plan....

Daher wäre mein Ansatz für jede Bedingung einen Query abzusetzen und die Ergebnisse in eine neue temporäre Tabelle zusammenzufassen, die ich dann mit PHP auf meiner Webseite als Gesamttabelle präsentieren kann (Anzahl Spiele, Teamname, Siege, Niederlagen, Punkte, Tordifferenz, etc.), wobei die Punkte, Tordifferenz errechnet werden - Die Anzahl der Spiele, Siege und Niederlagen würde ich gerne als "Count" aus der Tabelle entnehmen.....

Ergänzend: Ich besitze Grundwissen in DB/PHP, bin aber alles andere als ein Profi. Aus Erfahrung tendiere ich aber, soviel als Möglich bereits in die "Selects" zu verpacken und in PHP nur die Ausgabe zu realisieren.

So, ich hoffe mich klar und verständlich mitgeteilt zu haben und würde mich über jede Hilfestellung/Tipp freuen!

MFG
Wolfgang

 

[akretschmer]

roblem:
in einem einzigen Query kann ich nicht nach den Teams gruppieren und die Summen der Tore bilden, weil ich einerseits aus der Logik den Sieger ermittle und beide ID's (team1 + team2) in die selbe Teamtabelle joinen, zumindest fehlt mir dazu der Plan....

Zeig mal eine kleine Demo-Datentabelle und das, was Du Dir als Resultat wünscht.

 

[ViecFan]

Hallo!

zuerst danke für deine rasche Antwort! ;-)
Anbei habe ich Screenshots angehängt:
mysql_table --> ein paar Datensätze direkt in der Tabelle (DB)
workbench_screenshot --> eine Select-Variante in der ich alle betroffenen Datensätze auslese. Jeder Datensatz zeigt mir das Ergebnis eines Spieles - Heimteam(team1), Auswärtsteam(team2), die erzielten Tore des Heim(score1)+Auswärtsteams(score2), woraus sich der Sieger und auch die Tordifferenz ergibt. Ziel ist es eine Gesamttabelle zu erzeugen, in der die Teams nach Anzahl der Spiele, Punkte, Tordifferenz gereiht werden (ähnlich wie im Screenshot). Die Teamnamen und Logos hole ich mir über die jeweilige Team_ID aus einer anderen Tabelle. Ích habe schon viel mit verschiedensten Ansätzen "Selects" gebaut um die Logik abzubilden, allerdings keine Möglich herausgefunden um ALLES in eine Abfrage zu packen (was wahrscheinlich eh nicht gehen wird).
Vielleicht hast du einen Ansatz, wie ich es in Kombination MySql/PHP am besten angehen soll/kann!

Vielen Dank im Voraus
LG
Wolfgang

 

[akretschmer]

Ziel ist es eine Gesamttabelle zu erzeugen, in der die Teams nach Anzahl der Spiele, Punkte, Tordifferenz gereiht werden

Das ist mehr oder weniger eine Fleißaufgabe. Ich binncht fleißig und zu faul, aus den Bildern das alles auszulesen. Hier meine Tabellen:

test=*# select * from teams;
 id | name
----+-------
  1 | team1
  2 | team2
  3 | team3
  4 | team4
  5 | team5
  6 | team6
(6 rows)

test=*# select * from spiele ;
 team_heim | team_gast | heim | gast
-----------+-----------+------+------
  1 |  2 |  3 |  4
  2 |  3 |  5 |  4
  3 |  5 |  6 |  4
  3 |  6 |  7 |  2
  4 |  6 |  2 |  3
  1 |  6 |  2 |  1
  6 |  1 |  12 |  1
(7 rows)

Part 1, Anzahl der Spiele:

test=*# select *, (select count(1) from spiele where team_heim=teams.id) + (select count(1) from spiele where team_gast=teams.id) as spiele from teams ;
 id | name  | spiele
----+-------+--------
  1 | team1 |  3
  2 | team2 |  2
  3 | team3 |  3
  4 | team4 |  1
  5 | team5 |  1
  6 | team6 |  4
(6 rows)

Nach demselben Schema kannst den Rest auch ermitteln.

 

[ViecFan]

Guten Morgen!

Danke für deinen Lösungsansatz mit den Unterabfragen - wenn du noch Zeit erübrigen kannst/willst habe ich dir die 3 wesentlichen Tabellen im Anhang gepackt...
Ein Problem ergibt sich allerdings - nicht ALLE Teams (nnhl_bl_teams) sind in allen Spieltagen (nnhl_bl_match) vertreten(Spieltag ist m_id) . Ich kann dass aber nicht lösen, indem ich zbsp. alle Teams die durch den Select Count "0" als Ergebnis aufweisen einfach zu ignorieren, weil ja zu Beginn der Saison (hier als Spieltag definiert) vor den ersten Spielen alle "0" als Ergebnis werfen!
Kann ich den Join einfach umdrehen (nur Datensätze die in nnhl_bl_match mit der selben m_id vorkommen entsprechen Datensätzen der nnhl_bl_teams)?

Ich denke wahrscheinlich falsch und zu kompliziert - wobei die Schemas dieser Tabellen teilweise zum grausen sind und das Unterfangen nicht leichter machen! ;-)

Nochmals vielen Dank für deine Unterstützung, wenn es dir zu blöd wird = ist auch OK!!
LG
Wolfgang

 

[ukulele]

Ich kann dir mal meine Lösung aus unserem (MSSQL) Tippspiel posten. Das wirkt vieleicht umfangreich, ist aber eigentlich nicht so wild. Der Schlüssel liegt im UNION und naja, rank() kann MySQL nicht. Da musst du vieleicht mit PHP was basteln.

ALTER VIEW [dbo].[tsp_mannschaftswertung]
AS
SELECT    TOP 1000
        m.pk,
        rank() OVER (ORDER BY sum(m.Punkte) DESC,sum(m.Tore)-sum(m.Gegentore) DESC,sum(m.Tore) DESC,sum(m.Gegentore)) AS Platz,
        m.Mannschaft,
        sum(m.Tore) AS Tore,
        sum(m.Gegentore) AS Gegentore,
        sum(m.Tore) - sum(m.Gegentore) AS Tordifferenz,
        sum(m.Punkte) AS Punkte
FROM    (    SELECT    m.pk AS pk,
                    m.land AS Mannschaft,
                    isnull(s.tore1,0) AS Tore,
                    isnull(s.tore2,0) AS Gegentore,
                    (    CASE
                        WHEN    s.tore1 > s.tore2    THEN    3
                        WHEN    s.tore1 = s.tore2    THEN    1
                        ELSE    0
                        END ) AS Punkte
            FROM    tsp_mannschaft m,
                    tsp_spiele s
            WHERE    m.pk = s.fk_mannschaft1
            UNION ALL
            SELECT    m.pk AS pk,
                    m.land AS Mannschaft,
                    isnull(s.tore2,0) AS Tore,
                    isnull(s.tore1,0) AS Gegentore,
                    (    CASE
                        WHEN    s.tore2 > s.tore1    THEN    3
                        WHEN    s.tore2 = s.tore1    THEN    1
                        ELSE    0
                        END ) AS Punkte
            FROM    tsp_mannschaft m,
                    tsp_spiele s
            WHERE    m.pk = s.fk_mannschaft2 ) m
GROUP BY m.pk,m.Mannschaft
ORDER BY sum(m.Punkte) DESC,sum(m.Tore)-sum(m.Gegentore) DESC,sum(m.Tore) DESC,sum(m.Gegentore),m.Mannschaft

 

[ViecFan]

Hallo Ukele!

Das sieht sehr interessant aus und ist mir "fast" klar -- könntest du mir bitte noch deine beiden Tabellen-Schemen (Spaltennamen) dazu posten, dass ich mir ein besseres Bild machen kann!?

Vielen Dank für deinen Input
LG
Wolfgang

 

[ukulele]

Hier leicht gekürzt:

CREATE TABLE [dbo].[tsp_mannschaft](
    [pk] [uniqueidentifier] NOT NULL,
    [fahne] [image] NULL,
    [land] [varchar](30) NULL
PRIMARY KEY CLUSTERED
(
    [pk] ASC

CREATE TABLE [dbo].[tsp_spiele](
    [fk_mannschaft1] [uniqueidentifier] NULL,
    [fk_mannschaft2] [uniqueidentifier] NULL,
    [pk] [uniqueidentifier] NOT NULL,
    [start] [datetime] NULL,
    [tore1] [smallint] NULL,
    [tore2] [smallint] NULL,
PRIMARY KEY CLUSTERED
(
    [pk] ASC

Das ist aber eigentlich selbsterklärend und leicht anpassbar.

 

[ViecFan]

Hi,

jetzt hatte ich endlich wieder Zeit um euer KnowHow für mich zu nutzen! Und ich kann mich nur herzlichst bei Euch bedanken! Mein Ziel, alle Ergebnisse direkt aus dem Query zu erhalten ist Realität geworden. Zur Info nachfolgend noch mein angepasster Code:

SELECT  A.Logo, A.Mannschaft,
        count(1) as Spiele,
        sum(A.Tore) AS Tore,
        sum(A.Gegentore) AS Gegentore,
        sum(A.Tore) - sum(A.Gegentore) AS Tordifferenz,
        sum(A.Punkte) AS Punkte
FROM    (    SELECT T.id AS T,
                    T.t_name AS Mannschaft,
                    T.t_emblem AS Logo,
                    ifnull(M.score1,0) AS Tore,
                    ifnull(M.score2,0) AS Gegentore,
                    (    CASE
                        WHEN    M.score1 > M.score2  THEN    2
                        WHEN    M.score1 = M.score2  THEN    1
                        ELSE    0
                        END ) AS Punkte
            FROM    nnhl_bl_teams T,
                    nnhl_bl_match M
            WHERE    T.id = M.team1_id and M.m_played = 1 and M.m_id = 29
            UNION ALL
            SELECT  T.id AS T,
                    T.t_name AS Mannschaft,
                    T.t_emblem AS Logo,
                    ifnull(M.score2,0) AS Tore,
                    ifnull(M.score1,0) AS Gegentore,
                    (    CASE
                        WHEN    M.score2 > M.score1  THEN    2
                        WHEN    M.score2 = M.score1  THEN    1
                        ELSE    0
                        END ) AS Punkte
            FROM    nnhl_bl_teams T,
                    nnhl_bl_match M
            WHERE   T.id = M.team2_id and M.m_played = 1 and M.m_id = 29 ) A
group by A.Mannschaft
ORDER BY sum(A.Punkte) DESC,sum(A.Tore)-sum(A.Gegentore) DESC,sum(A.Tore) DESC,sum(A.Gegentore),A.Mannschaft;

Das Ergebnis als Screenshot im Anhang!

Nochmals vielen Dank für tolle und vorallem rasche Hilfe!!!



LG
Wolfgang