paulbrause: SUBQUERY & LIMIT

Hallöchen!

Ich steht im Moment total aufm Schlauch, vielleicht kann mir ja jemand einen Denkanstoss geben... ;)

Folgende Situation: es geht um ein Spiel, in dem über mehrere Runden die Teilnehmer Punkte bekommen. Die Abfrage der Gesamtpunktzahl, Spitzenreiter der jeweiligen Runde, höchste erreichte Punktzahl usw. ist ja auch kein Problem.

Hier mal der (sehr) vereinfachte Tabellenaufbau:

  
+=======+========+========+  
| SPIEL | NAME   | PUNKTE |  
+=======+========+========+  
| 1     | Bernd  | 8      |  
+-------+--------+--------+  
| 2     | Bernd  | 2      |  
+-------+--------+--------+  
| 3     | Bernd  | 10     |  
+-------+--------+--------+  
| 4     | Bernd  | 4      |  
+-------+--------+--------+  
| 5     | Bernd  | 1      |  
+-------+--------+--------+  
| 6     | Bernd  | 3      |  
+-------+--------+--------+  
| 7     | Bernd  | 9      |  
+-------+--------+--------+  
| 1     | Klaus  | 9      |  
+-------+--------+--------+  
| 3     | Klaus  | 4      |  
+-------+--------+--------+  
| 5     | Klaus  | 2      |  
+-------+--------+--------+  
| 7     | Klaus  | 0      |  
+-------+--------+--------+  
| 1     | Stefan | 3      |  
+-------+--------+--------+  
| 2     | Stefan | 2      |  
+-------+--------+--------+  
| 3     | Stefan | 7      |  
+=======+========+========+  

Nun möchte ich allerdings für jeden Mitspieler die Punkte der letzten 3 Runden, die dieser mitgespielt hat, zusammenaddiert anzeigen.

Also nicht die 3 letzten Runden allgemein (7,6,5), sondern für jeden einzelnen Mitspieler (Bernd -> 7,6,5, Klaus 7,5,3, Stefan 3,2,1).

Als Ergebnis hätte ich also gerne eine Tabelle, die mir die Punkte, der jeweils letzten 3 mitgespielten Runden anzeigt (keine Gesamtpunktzahl !), also sowas wie:

  
+==================+  
| ERREICHTE PUNKTE |  
+==================+  
| Bernd  | 13      |  
+--------+---------+  
| Stefan | 12      |  
+--------+---------+  
| Klaus  | 6       |  
+==================+  

Ich hab's dann mal auf folgende Weise probiert, klappt aber nicht:

  
SELECT t.benutzer, SUM(t.punkte) AS punkte  
FROM test t  
WHERE t.spiel IN  
(  
  SELECT q.spiel  
  FROM test q  
  WHERE q.benutzer = t.benutzer  
  ORDER BY q.spiel  
  LIMIT 3  
)  
GROUP BY t.benutzer  
ORDER BY punkte DESC  

LIMIT darf man wohl nicht in einem Subquery verwenden... :(

So, meine Frage nun:
Wie kann man's noch lösen? Geht's eigentlich überhaut in einer einzigen Abfrage? Oder muss ich mit einer temporären Tabelle arbeiten?

Vielen Dank schon mal,
paulbrause

  1. yo,

    LIMIT darf man wohl nicht in einem Subquery verwenden... :(

    leider ist das so, ein umstand, den ich bei mysql auch immer gerne vergesse. aber in einem anderen beitrag hat jemand kluges die Unterabfrage mit der LIMIT Klausel in die FROM klausel eingebaut und das soll angeblich wohl gehen. dann musst du nur noch entsprechend den JOIN über die spalte spiel zur tabelle test setzen und berichten, ob es klappt oder nicht.

    Ilja

    1. n'abend,

      LIMIT darf man wohl nicht in einem Subquery verwenden... :(

      Korrekt.

      leider ist das so, ein umstand, den ich bei mysql auch immer gerne vergesse. aber in einem anderen beitrag hat jemand kluges die Unterabfrage mit der LIMIT Klausel in die FROM klausel eingebaut und das soll angeblich wohl gehen. dann musst du nur noch entsprechend den JOIN über die spalte spiel zur tabelle test setzen und berichten, ob es klappt oder nicht.

      Ich maße mir mal nicht an der besagte Kluge zu sein, aber interessant ist, dass man eine Query an sich als Tabelle handhaben kann (was ich gerne als "Temporärer View" bezeichne, MySQL nennt das ganze unbenannte Views). Das ist für mich nur all zu oft der Lebensretter, wenn ich mal wieder nicht um eine LIMIT-Subquery herumkomme.

      In PostGreSQL haben wir vor einigen Monaten festgestellt, dass wir Abfragen durch diese unbenannten Views erheblich beschleunigen konnten, wenn wir mehrere große Tabellen JOINen mussten, aus denen wir aber nur eine kleine Menge an Datensätzen brauchten.

      weiterhin schönen abend...

      --
      #selfhtml hat ein Forum?
      sh:( fo:# ch:# rl:| br:> n4:& ie:{ mo:} va:) de:] zu:} fl:( ss:? ls:[ js:|
      1. echo $begrüßung;

        In PostGreSQL haben wir vor einigen Monaten festgestellt, dass wir Abfragen durch diese unbenannten Views erheblich beschleunigen konnten, wenn wir mehrere große Tabellen JOINen mussten, aus denen wir aber nur eine kleine Menge an Datensätzen brauchten.

        Interessant wäre es zu wissen, wo die Subquery vorher stand, bevor du sie in den FROM-Teil umgezogen hattest.

        echo "$verabschiedung $name";

    2. Hallo!

      Danke erstmal für die flotte Antwort, allerdings hilft's mir bei meinem Problem wohl zunächst nicht.

      Ich habe, wie in diesem Thread ([link http://forum.de.selfhtml.org/my/?t=170752&m=1116620&readmode=nested#m1116620]) beschrieben, den Subquery in einen Join umgewandelt:

        
      SELECT t.benutzer, SUM(t.punkte) AS punkte  
      FROM test t  
      JOIN  
      (  
        SELECT q.spiel  
        FROM test q  
        WHERE q.benutzer = t.benutzer  
        ORDER BY q.spiel  
        LIMIT 3  
      ) AS s  
      WHERE t.spiel = s.spiel  
      GROUP BY t.benutzer  
      ORDER BY punkte DESC  
      
      

      Allerdings bekomme ich nun immer wieder den MySQL-Fehler "Unknown table 't' in where clause", da man wohl nicht im Join auf eine ausserhalb liegende Tabelle referenzieren kann?!?

      Aber diese Referenz brauche ich ja, da für jeden einzelnen Mitspieler die letzten 3 mitgespielten Runden ermittelt werden und nicht für die Allgemeinheit, oder?!?

      Oder hab ich da ein Verständnisproblem?

      Danke,
      paulbrause

      1. n'abend,

        ich greife meiner anderen Antwort mal vorweg:

        SELECT t.benutzer, SUM(t.punkte) AS punkte
        FROM test t
        JOIN
        (
          SELECT q.spiel
          FROM test q
          WHERE q.benutzer = t.benutzer
          ORDER BY q.spiel
          LIMIT 3
        ) AS s
        WHERE t.spiel = s.spiel
        GROUP BY t.benutzer
        ORDER BY punkte DESC

        
        >   
        > Allerdings bekomme ich nun immer wieder den MySQL-Fehler "Unknown table 't' in where clause", da man wohl nicht im Join auf eine ausserhalb liegende Tabelle referenzieren kann?!?  
          
        Diese unbenannten Views werden VOR dem JOIN ausgeführt, was bedeutet, dass wir da nicht korrelierend drauf agieren können.  
          
        Die "innere" Abfrage wird also ausgeführt bevor die Datensätze geJOINt werden. Das bedeutet, dass du innerhalb eines unbenannten Views nicht auf andere Tabellen (außer sie sind darin geschachtelt) zugreifen kannst.  
          
        
        > Aber diese Referenz brauche ich ja, da für jeden einzelnen Mitspieler die letzten 3 mitgespielten Runden ermittelt werden und nicht für die Allgemeinheit, oder?!?  
          
        Genau \*das\* ist das Problem das ich gerade zu lösen versuche. Allerdings kommt hier nur eine reichlich eklige Abfrage raus. Dauert noch nen Moment...  
          
        weiterhin schönen abend...
        
        -- 
        #selfhtml hat ein Forum?  
          
        sh:( fo:# ch:# rl:| br:> n4:& ie:{ mo:} va:) de:] zu:} fl:( ss:? ls:[ js:|
        
        1. Diese unbenannten Views werden VOR dem JOIN ausgeführt, was bedeutet, dass wir da nicht korrelierend drauf agieren können.

          Klar, hatte ich nicht bedacht...

          Genau *das* ist das Problem das ich gerade zu lösen versuche. Allerdings kommt hier nur eine reichlich eklige Abfrage raus. Dauert noch nen Moment...

          Wenn ich gewusst hätte, dass das nur sooo umständlich zu lösen ist, dann hätt ich's gelassen... aber einmal angefangen wird nu nicht aufgegeben. :D

          weiterhin schönen abend...

          Dir auch & vielen Dank für die Hilfe...

  2. n'abend,

    Nun möchte ich allerdings für jeden Mitspieler die Punkte der letzten 3 Runden, die dieser mitgespielt hat, zusammenaddiert anzeigen.

    Das ist mithilfe unbenannter Views kein Problem. Du würdest in einem unbekannten View die drei höchsten Spiel-nummern auswählen, das dann gegen deine Spielstand-Tabelle JOINen (wodurch alle Datensätze mit einer anderen Spiel-ID rausgeworfen werden würden) und darauf dann die Summe bilden.

    Also nicht die 3 letzten Runden allgemein (7,6,5), sondern für jeden einzelnen Mitspieler (Bernd -> 7,6,5, Klaus 7,5,3, Stefan 3,2,1).

    Das verkompliziert die Sache allerdings gewalttätigst. Diese unbenannten Views werden VOR dem JOIN ausgeführt, was bedeutet, dass wir da nicht korrelierend drauf agieren können. Da fällt mir auf Anhieb nur eine sehr widerliche Abfrage ein, wenn man nicht mit temporären Tabellen arbeiten möchte oder kann.

    Wir brauchen das letzte Spiel für jeden Benutzer:

    SELECT s.name, MAX(s.spiel)  
      FROM spiele_tabelle s  
     GROUP BY name;
    

    Wir brauchen das vorletzte Spiel für jeden Benutzer:

    SELECT s.name, MAX(s.spiel)  
      FROM spiele_tabelle s  
     WHERE s.spiel < (  
           SELECT MAX(s1.spiel)  
             FROM spiele_tabelle s1  
            WHERE s1.name = s.name  
           )  
     GROUP BY name;
    

    Wir brauchen das vorvorletzte Spiel für jeden Benutzer:

    SELECT s.name, MAX(s.spiel)  
      FROM spiele_tabelle s  
     WHERE s.spiel < (  
           SELECT MAX(s1.spiel)  
             FROM spiele_tabelle s1  
            WHERE s1.name = s.name  
              AND s1.spiel < (  
                  SELECT MAX(s2.spiel)  
                    FROM spiele_tabelle s2  
                   WHERE s2.name = s.name  
                  )  
           )  
     GROUP BY name;
    

    Das sind drei getrennte Abfragen, die dummerweise auch noch das gleiche immer wieder machen. Wie die Redundanz in den Abfragen aufgelöst werden könnte, entzieht sich gerade meiner Vorstellungskraft. Die Ergebnisse der drei Abfragen können wir mittels UNION zusammenfassen. Damit hätten wir dann die letzten drei Spiele eines jeden Spielers ermittelt. Bleibt also nur noch die Bildung der Summe.

    SELECT s.name, SUM(s.punkte) AS punkte  
      FROM spiele_tabelle s  
      JOIN (  
             SELECT s.name, MAX(s.spiel)  
               FROM spiele_tabelle s  
              GROUP BY name  
      
             UNION  
      
             SELECT s.name, MAX(s.spiel)  
               FROM spiele_tabelle s  
              WHERE s.spiel < (  
                    SELECT MAX(s1.spiel)  
                       FROM spiele_tabelle s1  
                      WHERE s1.name = s.name  
                     )  
               GROUP BY name  
      
             UNION  
      
             SELECT s.name, MAX(s.spiel)  
               FROM spiele_tabelle s  
              WHERE s.spiel < (  
                    SELECT MAX(s1.spiel)  
                      FROM spiele_tabelle s1  
                     WHERE s1.name = s.name  
                       AND s1.spiel < (  
                           SELECT MAX(s2.spiel)  
                             FROM spiele_tabelle s2  
                            WHERE s2.name = s.name  
                           )  
                    )  
              GROUP BY name  
           ) AS t  
        ON ( t.name = s.name AND t.spiel = s.spiel )  
     GROUP BY s.name;  
    
    

    Natürlich ist diese Query frei heruntergeschrieben und nicht getestet.

    weiterhin schönen abend...

    --
    #selfhtml hat ein Forum?
    sh:( fo:# ch:# rl:| br:> n4:& ie:{ mo:} va:) de:] zu:} fl:( ss:? ls:[ js:|