SUBQUERY & LIMIT

[paulbrause]

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

[Ilja]

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

[globe]

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...

[dedlfix]

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";

[paulbrause]

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

[globe]

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:|

[paulbrause]

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...

[globe]

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...