Ankündigung

Einklappen
Keine Ankündigung bisher.

[Erledigt] Summation über Unterabfragen - Mysql

Einklappen

Neue Werbung 2019

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • [Erledigt] Summation über Unterabfragen - Mysql

    Hallo,
    Ich steh leider vor einem Problem bei einer recht komplexen Abfrage und brauch deswegen etwas Hilfe und hoffe sie hier zu finden
    Also zunächst ersteinmal vielleicht zum vorhandenen Kontext, und zwar geht es um eine Fussballvereinsseite, bei der ich nun eine Statistik der aktiven Spieler generieren möchte, also eine Übersicht über Tore und Spieleinsätze, geordnet nach den jeweiligen Spielarten(Pokalspiel, Freundschaftsspiel, etc).
    So da habe ich 3 Tabellen: `mitglied`, `spiel` und `spiel_spieler`
    `mitglied` ist die Übersicht über alle Spieler
    `spiel` ist die Übersicht über alle Spiele
    `spiel_spieler` stellt die Verknüpfung von beidem dar und hält fest, welcher spieler bei welchem spiel dabei war und wieviel tore er geschossen hat.
    So meine prinzipielle Abfrage sieht so aus:
    Code:
    SELECT `vorname`,SUM(`tore`)
    FROM `mitglied` , `spiel_spieler`,`spiel`
    WHERE `mitglied`.`id` = `mitgliednr` AND `spiel`.`id`=`spielnr` AND `aktiv` =1
    GROUP BY `vorname`
    Damit krieg ich ja aber nur die insgesamt geschossenen Tore und nun stellt sie die Frage, wie ich die Unterscheidung nach der Spielart in diese Abfrage einbaue.
    Ich dachte schon an eine Unterabfrage, über die ich dann summiere, aber das warf einen Fehler
    Mfg jume

  • #2
    Im Prinzip brauchen wir für eine Lösung die kompletten CREATE TABLE der drei Tabellen, da nicht klar ist, in welcher Tabelle "spielart" ist.

    Deshalb geraten die folgende Lösung..

    Code:
    SELECT `vorname`,`spiel`.spielart, SUM(`tore`)
    FROM `mitglied` , `spiel_spieler`,`spiel`
    WHERE `mitglied`.`id` = `mitgliednr` AND `spiel`.`id`=`spielnr` AND `aktiv` =1
    GROUP BY `vorname`,`spiel`.spielart
    Stimmt die "Richtung"?

    Grüße
    Thomas

    Kommentar


    • #3
      ja, die Richtung stimmt und sorry wegen der spielart, ja die ist in der Tabelle spiel.
      Also deine Abfrage bringt mir erstmal das gewünschte Ergebnis. Aber wie geb ich das jetzt in php aus.
      Also auf meiner Seite möchte ich dann eine Tabelle, wo vorn alle Spieler stehen und dann hinten die Tore nach Spielart.
      Aber das würde bei deiner Abfrage nicht so ganz funktionieren, glaub ich.
      Ich hatte eben noch ein bisschen rumprobiert und habe folgende Abfrage hinbekommen, die mir genau das gewünschte ausgibt, allerdings glaube ich, ist, das diese performancemäßig nicht ganz so gut is, Abfrage dauerte beim ersten Aufruf knapp 2s und später dann aber nur noch 0,001s oder so

      Code:
      SELECT CONCAT( `vorname` , ' ', `nachname` ) AS `name` , (
      
      SELECT SUM( `tore` )
      FROM `spiel` , `spiel_spieler`
      WHERE `spiel`.`id` = `spielnr`
      AND `spiel`.`spielart` = 'P'
      AND `mitglied`.`id` = `mitgliednr`
      ) AS `p_tore` , (
      
      SELECT SUM( `tore` )
      FROM `spiel` , `spiel_spieler`
      WHERE `spiel`.`id` = `spielnr`
      AND `spiel`.`spielart` = 'M'
      AND `mitglied`.`id` = `mitgliednr`
      ) AS `m_tore` , (
      
      SELECT SUM( `tore` )
      FROM `spiel` , `spiel_spieler`
      WHERE `spiel`.`id` = `spielnr`
      AND `spiel`.`spielart` = 'F'
      AND `mitglied`.`id` = `mitgliednr`
      ) AS `f_tore`
      FROM `mitglied` , `spiel_spieler` , `spiel`
      WHERE `mitglied`.`id` = `mitgliednr`
      AND `aktiv` =1
      GROUP BY `name`

      Kommentar


      • #4
        Ob eine SQL-Abfrage von MySQL performant abgearbeitet werden kann, läßt sich mit EXPLAIN ermitteln. Mach mal

        Code:
        EXPLAIN SELECT ...
        Und zeige das Ergebnis des EXPLAIN als Bild oder gut formatierter Text an, oder hänge es als reine Textdatei-Attachment an Deine Nachricht (dass ist das einfachste).

        Grüße
        Thomas

        Kommentar


        • #5
          Ich habs mal als Bild gemacht:

          Kommentar


          • #6
            Zwei Dinge:

            a) Meiner Meinung nach fehlt hier im SQL noch eine Verbindung (JOIN) zwischen den Tabellen spiel und spiel_spieler .

            Code:
            ...
            SELECT SUM( `tore` )
            FROM `spiel` , `spiel_spieler`
            WHERE `spiel`.`id` = `spielnr`
            AND `spiel`.`spielart` = 'P'
            AND `mitglied`.`id` = `mitgliednr`
            ) AS `p_tore` , (
            ...
            b) Lege mal einen zusätzlichen Index an:

            Code:
            CREATE INDEX sx_spiel_01 ON spiel (spielart, id);
            und zeige dann nochmal den EXPLAIN

            EDIT

            oder ist der JOIN zwischen spiel und spiel_spieler hier (ohne komplette CREATE TABLE kann ich es nur raten)

            Code:
            ...
            SELECT SUM( `tore` )
            FROM `spiel` , `spiel_spieler`
            WHERE `spiel`.`id` = `spiel_spieler`.`spielnr`
            AND `spiel`.`spielart` = 'P'
            AND `mitglied`.`id` = `mitgliednr`
            ) AS `p_tore` , (
            ...

            Grüße
            Thomas

            Kommentar


            • #7
              Da du auf die CREATE TABLE hingewiesen hast, wie komm ich da im Nachhinein ran?

              deine Vermutung ist richtig, das WHERE `spiel`.`id` = `spiel_spieler`.`spielnr` ist der JOIN

              hier Explain-nr2 nachdem einfügen des Index


              EDIT: hab den index id_art genannt

              Kommentar


              • #8
                Zitat von jume Beitrag anzeigen
                Da du auf die CREATE TABLE hingewiesen hast, wie komm ich da im Nachhinein ran?
                Bspw. in dem du die Tabellenstruktur mittels phpMyAdmin exportierst.
                [SIZE="1"]RGB is totally confusing - I mean, at least #C0FFEE should be brown, right?[/SIZE]

                Kommentar


                • #9
                  ok, danke
                  dann hier die CREATE TABLE:
                  Code:
                  CREATE TABLE  `spiel_spieler` (
                    `spielnr` int(10) unsigned NOT NULL,
                    `mitgliednr` int(10) unsigned NOT NULL default '0',
                    `position` varchar(2) NOT NULL,
                    `trikotnr` varchar(2) default NULL,
                    `tore` tinyint(2) NOT NULL default '0',
                    PRIMARY KEY  (`spielnr`,`mitgliednr`)
                  ) ENGINE=MyISAM DEFAULT CHARSET=utf8
                  Code:
                  CREATE TABLE `spiel` (
                    `id` int(10) unsigned NOT NULL auto_increment,
                    `spielart` char(1) NOT NULL,
                    `ort` char(1) NOT NULL,
                    `gegner` text NOT NULL,
                    `datetime` datetime NOT NULL,
                    `ergebnis` varchar(20) default NULL,
                    `treff` time NOT NULL,
                    `treffpunkt` text NOT NULL,
                    `bericht` text,
                    `aa` int(5) default NULL,
                    `ao` tinyint(1) default NULL,
                    `kapitaen` int(10) unsigned default NULL,
                    `waesche` int(10) unsigned default NULL,
                    `album` int(10) default NULL,
                    `notiz` text,
                    PRIMARY KEY  (`id`),
                    UNIQUE KEY `album` (`album`),
                    KEY `id_art` (`id`,`spielart`)
                  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8
                  Code:
                  CREATE TABLE `mitglied` (
                    `id` int(10) unsigned NOT NULL auto_increment,
                    `vorname` varchar(20) NOT NULL,
                    `nachname` varchar(20) NOT NULL,
                    `spitzname` varchar(10) default NULL,
                    `gruppe` varchar(3) NOT NULL,
                    `passnr` varchar(9) default NULL,
                    `gebdatum` date NOT NULL,
                    `telefon` varchar(255) default NULL,
                    `email` varchar(50) default NULL,
                    `handy` varchar(255) default NULL,
                    `aktiv` tinyint(1) NOT NULL default '1',
                    `bild` varchar(255) default NULL,
                    PRIMARY KEY  (`id`),
                    KEY `email` (`email`)
                  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8

                  Kommentar


                  • #10
                    Der Index "id_art" ist jetzt leider nicht ganz so, wie ich ihn wollte. Bitte den "id_art" wieder entfernen und den Index "spielart_id" erzeugen.

                    Code:
                    CREATE INDEX spielart_id ON spiel (spielart, id);
                    Wie sieht jetzt der EXPLAIN aus?

                    EDIT
                    Stimmt die Ausgabe noch, wenn Du aus dem GROUP BY name ein ORDER BY name machst?

                    Grüße
                    Thomas

                    Kommentar


                    • #11
                      sry, wusste nicht das das einen Unterschied macht.
                      hier nochmal mit index id_art

                      Kommentar


                      • #12
                        Okay, dass sieht schon etwas besser aus..

                        noch zwei Dinge:

                        a)
                        Stimmt die Ausgabe noch, wenn Du aus dem GROUP BY name ein ORDER BY name machst?

                        b) noch einen zusätzlichen Index

                        Code:
                        CREATE INDEX spiel_mit_tor  ON spiel_spieler (`mitgliednr`, `spielnr`, `tore`)
                        Wie sieht jetzt der EXPLAIN aus und die Geschwindigkeit der Abfrage?

                        Grüße
                        Thomas

                        Kommentar


                        • #13
                          Nein, dann bekomme ich rund 10000 ergebniszeilen, was definitiv nicht stimmt

                          ok hier explain mit dem neuen index

                          Kommentar


                          • #14
                            Der neue Index spiel_mit_tor erscheint zwar in "possible keys", aber MySQL nutzt ihn nicht siehe "key", schade. Der Index spiel_mit_tor kann jetzt oder später wieder entfernt werden Kannst ihn ja noch mal eine Weile stehen lassen, vielleicht kommt er später zum Zuge.

                            So bis hier war es einfach.

                            Wie sieht inzwischen die Geschwindigkeit aus?

                            Grüße
                            Thomas

                            Kommentar


                            • #15
                              nochmal zu dem GROUP BY

                              hier ist eventuell etwas falsch..

                              anstatt
                              Code:
                              ...
                              FROM `mitglied` , `spiel_spieler` , `spiel`
                              WHERE `mitglied`.`id` = `mitgliednr`
                              AND `aktiv` =1
                              GROUP BY `name`

                              mal dies ausprobieren..
                              Code:
                              ...
                              FROM `mitglied` 
                              WHERE `mitglied`.`id` = `mitgliednr`
                              AND `aktiv` =1
                              ORDER BY `name`
                              geht dass, stimmt dann das Ergebnis noch..?

                              Grüße
                              Thomas

                              Kommentar

                              Lädt...
                              X