114298
Goto Top

MySQL: Zwei Spalten in einer View zählen

Hallo,

ich habe eine Fussballdatenbank mit der Tabelle 'Tore' die wie folgt aussieht

TorID Spiel Tor Vorlage
1MannschaftA-MannschaftBSpielerASpielerB
2MannschaftA-MannschaftBSpielerASpielerC
3MannschaftA-MannschaftBSpielerBSpielerD
4MannschaftA-MannschaftBSpielerESpielerC


Ich möchte nun aus Abfrage wo ich pro Spieler sehe, wie viele er Tore geschossen hat und wie viele Vorlagen er gegeben hat


Mein View sollte so aussehen

NameToreVorlagenScorerpunkte
SpielerA202
SpielerB112
SpielerC022
SpielerD011
SpielerE101

Die Tore und und Vorlagen separat zu bestimmen ist kein problem, aber ich bekomme die irgendwie nicht zusammen.

Vielleicht hat einer einen Ansatz

Vielen Dank face-smile

Content-Key: 318172

Url: https://administrator.de/contentid/318172

Ausgedruckt am: 19.03.2024 um 06:03 Uhr

Mitglied: ukulele-7
ukulele-7 19.10.2016 um 08:38:00 Uhr
Goto Top
Super wäre eine weitere Tabelle die alle Spieler führt und bei Tor und Vorlage nur ein Fremdschlüssel. Es geht natürlich auch ohne auf verschiedenen Wegen.
SELECT coalesce(t1.Spieler,t2.Spieler) AS Spieler, t1.Tore, t2.Vorlagen
FROM ( SELECT Tor AS Spieler, count(*) AS Tore FROM Tore GROUP BY Tor ) t1
FULL OUTER JOIN ( SELECT Vorlage AS Spieler, count(*) AS Vorlagen FROM Tore GROUP BY Vorlage ) t2
ON t1.Spieler = t2.Spieler
Mitglied: Biber
Biber 19.10.2016 um 10:37:00 Uhr
Goto Top
Moin memo66,

nur eine Ergänzung zu ukulele-7s Lösung:

Der View selbst wäre dann in etwa so
Create View highscorer as (
Select Spieler, Tore, Vorlagen, tore+vorlagen as Scorerpunkte From (
SELECT coalesce(t1.Spieler,t2.Spieler) AS Spiele
       , coalesce( t1.Tore, 0) as Tore
       , coalesce( t2.Vorlagen, 0) as Vorlagen
FROM (
   SELECT Tor       AS Spieler
         , count(*) AS Tore
      FROM Tore
      GROUP BY Tor
      ) t1
  FULL OUTER JOIN (
   SELECT Vorlage  AS Spieler
        , count(*) AS Vorlagen
    FROM Tore
    GROUP BY Vorlage
    ) t2
      ON t1.Spieler = t2.Spieler
) whatever
ORDER by 4, 1
);
Wobei die Anweisung "ORDER BY" in einem View zwar erlaubt ist in den meisten DBMSen, aber eigentlich nicht Bestandteil eines Views ist.
Diese Zeile also lieber weglassen und beim Aufrufen des Views ergänzen.

Grüße
Biber
Mitglied: 114298
114298 19.10.2016 um 17:51:48 Uhr
Goto Top
Funktioniert leider noch nicht ganz, bekomme folgende Fehlermeldung

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Full Outer Join (SELECT Vorlage as Spieler, count(*) AS Vorlagen FROM tore Wh' at line 3

Wenn ich das Full outer join durch ein natural join ersetze funktioniert es. Bekomme natürlich nicht das Ergebnis das ich brauche.

Eine Idee?
Mitglied: Biber
Biber 19.10.2016 um 21:34:48 Uhr
Goto Top
Moin Memo66,

ach ja... mySQL kennt ja keinen FULL OUTER JOIN.

Ok, ANSI-SQL war ja auch nur ein Vorschlag und Standards ´werden total überbewertet. face-wink

Dann müssen wir uns halt einen FULL OUTER JOIN aus einem LEFT JOIN, einem RIGHT JOIN und einem UNION ALL zusammenschreddern.

Create View highscorer as (
Select Spieler, Tore, Vorlagen, tore+vorlagen as Scorerpunkte From (
SELECT coalesce(t1.Spieler,t2.Spieler) AS Spiele
       , coalesce( t1.Tore, 0) as Tore
       , coalesce( t2.Vorlagen, 0) as Vorlagen
FROM (
   SELECT Tor       AS Spieler
         , count(*) AS Tore
      FROM Tore
      GROUP BY Tor
      ) t1
  LEFT OUTER JOIN (
   SELECT Vorlage  AS Spieler
        , count(*) AS Vorlagen
    FROM Tore
    GROUP BY Vorlage
    ) t2
      ON t1.Spieler = t2.Spieler
UNION ALL
SELECT Tor       AS Spieler
         , count(*) AS Tore
      FROM Tore
      GROUP BY Tor
      ) t3
  RIGHT OUTER JOIN (
   SELECT Vorlage  AS Spieler
        , count(*) AS Vorlagen
    FROM Tore
    GROUP BY Vorlage
    ) t4
      ON t3.Spieler = t4.Spieler
    WHERE t3.Spieler is NULL
) whatever
ORDER by 4, 1
);
Wäre jedenfalls meine erste spontane Idee.

Grüße
Biber
Mitglied: ukulele-7
ukulele-7 20.10.2016 um 10:49:07 Uhr
Goto Top
Ich würde dann eher einen anderen Weg gehen:
SELECT t2.Spieler,
sum(CASE WHEN t3.Tor IS NOT NULL THEN 1 ELSE 0 END) AS Tore,
sum(CASE WHEN t4.Vorlage IS NOT NULL THEN 1 ELSE 0 END) AS Vorlagen
FROM (
SELECT DISTINCT t1.Spieler
FROM (
SELECT Tor AS Spieler FROM Tore
UNION ALL
SELECT Vorlage AS Spieler FROM Tore
) t1
) t2
LEFT JOIN Tore t3 ON t2.Spieler = t3.Tor
LEFT JOIN Tore t4 ON t2.Spieler = t4.Vorlage
GROUP BY t2.Spieler
Mitglied: Biber
Biber 20.10.2016 aktualisiert um 11:35:30 Uhr
Goto Top
Moin ukulele-7,

dein Ansatz ist natürlich einleuchtender und nachvollziehbarer (unter der Voraussetzung, es gibt keine "Spieler"-Stammdatentabelle).

Da ist es die richtige Strategie, erstmal alle Spieler rauszufieseln, die überhaupt in der "Tore"-Tabelle erwähnt werden und im zweiten Schritt zu ermitteln, warum sie denn erwähnt werden.

Meine Erweiterung bzw meine FULL OUTER JOIN-Simulation war auch mehr eine Trotzreaktion, weil... kann doch nicht wahr sein, dass erwachsene Datenbankimplementierungen nicht mal den Minimalkonsens ANSI-SQL umgesetzt haben.

Von daher: meinen Workaround nicht allzu ernst nehmen.

Grüße
Biber
Mitglied: ukulele-7
ukulele-7 20.10.2016 um 14:20:41 Uhr
Goto Top
Okay ich verstehe deine Abneigung gegenüber MySQL, ich arbeite fast gar nicht damit eher MSSQL. Ich hab den FULL OUTER JOIN auch nur da rein geschrieben weil ich ihn gegooglet habe, gebraucht habe ich den selbst noch nie^^
Mitglied: Biber
Biber 20.10.2016 aktualisiert um 16:20:02 Uhr
Goto Top
Moin ukulele-7,

meine Abneigung gegen mySQL ist gar nicht so gross.
War mehr ein nicht unterdrücktes Kopfschütteln, weil in mySQL einerseits wirklich viele Sonderlocken zusätzlich implementiert sind, andererseits aber Features fehlen, die du überall als selbstverständlich voraussetzt.

Ungefähr wie bei meinem Lieblings-Gebrauchtwagenhändler...
Dann werd ich solange vor dem Kauf zugeschwatzt mit Details der genialen Einparkhilfe, dem integrierten Staumelder mit der Synchronstimme von Lisa Simpson und dem beheizbaren stufenlos verstellbaren Bremspedal...

Nach dem Kauf dagegen denke ich "Hmm, vielleicht hätte ich doch auch fragen sollen, ob das Autoradio angeschlossen ist, die Scheibenwischer funktionieren und warum ich zum Anlassen immer das rote und das blaue Kabel aneinanderhalten muss.."

Andererseits...

Bei einer normalen Tabellenstruktur (also mit einer referenzierbaren Spieler-Tabelle) hätte auch niemand einen FULL OUTER JOIN gebraucht oder danach gesucht.

Grüße
Biber
Mitglied: ukulele-7
ukulele-7 21.10.2016 um 08:56:27 Uhr
Goto Top
Richtig der FULL OUTER JOIN ist selten, ich finde es auch nicht dramatisch wenn der fehlt. Echte Features kann ich aber bei MySQL keine entdecken, nur vermissen. Das einzige was ich als angenehm empfinde ist group_concat() für Gruppierungen, das ist in MSSQL nur recht komplex über XMLPATH möglich.

Wenn ich die absolut freie Wahl hätte würde ich sagen Postgre schlägt jedes MySQL in allen Belangen um längen. Das einzige was mich bei MSSQL hält ist die Unterstützung von vorhandener Software und der Zugriff aufs Dateisystem, das habe ich mal mit PG versucht das lief nicht brauchbar.
Mitglied: 114298
114298 21.10.2016 um 19:47:00 Uhr
Goto Top
Hallo zusammen,

vielen Dank für eure Hilfe.
Leider liefert die Abfrage von Ukulele ein falsches Ergebnis und die von Biber habe ich noch nicht zum laufen bekommen (irgendwo ist eine Klammer zu viel oder zu wenig)

Das ist aber ein guter Ansatz für mich, vielen Dank. Falls ich das die Abfrage hinbekomme, poste ich diese hier.

Ich bin blutiger Anfänger was SQL angeht. Ich habe mir ein Buch gekauft und versuche nun mir damit SQL beizubringen.
Ich habe eine MacBook und habe bisschen gegoogelt und bin auf MySQL gestoßen. Bin aber offen für andere kostenlose Server für Mac OS.

Zu meiner Tabelle:

Ich habe eine Tabelle 'Kader' mit allen Spielern und einer ID
In einer zweiten Tabelle 'Tore1' habe ich die jeweiligen Einträge mit der SpielerID als Fremdschlüssel.

Was ich mache, ich erstelle ein zweite Tabelle 'Tore2' (siehe oben) wo ich mir die Namen der Spieler hole.
Wie gesagt, ich bin ein blutiger Anfänger, ich weiß nicht ob man das so in der SQL Welt macht.

Bin dankbar für jeden Hinweis face-smile

Grüße Memo
Mitglied: Biber
Biber 22.10.2016 aktualisiert um 18:50:50 Uhr
Goto Top
Moin Memo,

sorry, wenn in meinem ungetesteten Schnipsel irgendwo eine Klammer zuviel oder zuwenig.
In der Stadt, in der ich wohne "ischa Freimaak", deshalb werde ich die heute auch nicht mehr durchzählen (können).
Wenn aber die grundsätzliche Idee, das Prinzip hinter dem Entwurf bei dir ankam, dann soll das auch reichen.

wenn du jetzt die Tabellem "Kader" und "Tore2" hast, dann ist das jedenfalls ein besserer Ausgangspunkt.

Jetzt kannst du direkt von der "Kader"-Tabelle aus verjoinen ohne Verrenkungen.
Sinngemäß, soweit ich das heute hinbekomme:
SELECT kader.Spielername, 
                SUM( CASE WHEN Tore2t.Tor          is NULL THEN  0 ELSE 1 END) as AnzTore,
                SUM( CASE WHEN Tore2v.Vorlage is NULL THEN  0 ELSE 1 END) as AnzVorlagen
FROM Kader 
LEFT JOIN Tore2 as Tore2t ON Kader.Spieler_id =Tore2t.Tor
LEFT JOIN Tore2 as Tore2v ON Kader.Spieler_id =Tore2v.Vorlage 
GROUP BY kader.spieler_Id

Okay, die Feldnamen können bei dir leicht abweichen, sollten aber wiedererkennbar sein.

Grüße
Biber
, der nachher bestimmt in Halle 7 sein wird