tag:forum.selfhtml.org,2005:/self SUBQUERY & LIMIT – SELFHTML-Forum 2008-05-07T17:55:12Z https://forum.selfhtml.org/self/2008/may/7/subquery-limit/1242661?srt=yes#m1242661 paulbrause paulbrause@googlemail.com 2008-05-07T13:53:14Z 2008-05-07T13:53:14Z SUBQUERY & LIMIT <p>Hallöchen!</p> <p>Ich steht im Moment total aufm Schlauch, vielleicht kann mir ja jemand einen Denkanstoss geben... ;)</p> <p>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.</p> <p>Hier mal der (sehr) vereinfachte Tabellenaufbau:</p> <pre><code class="block"> +=======+========+========+ | 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      | +=======+========+========+ </code></pre> <p>Nun möchte ich allerdings für jeden Mitspieler die Punkte der letzten 3 Runden, die dieser mitgespielt hat, zusammenaddiert anzeigen.</p> <p>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).</p> <p>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:</p> <pre><code class="block"> +==================+ | ERREICHTE PUNKTE | +==================+ | Bernd  | 13      | +--------+---------+ | Stefan | 12      | +--------+---------+ | Klaus  | 6       | +==================+ </code></pre> <p>Ich hab's dann mal auf folgende Weise probiert, klappt aber nicht:</p> <pre><code class="block"> 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 </code></pre> <p>LIMIT darf man wohl nicht in einem Subquery verwenden... :(</p> <p>So, meine Frage nun:<br> Wie kann man's noch lösen? Geht's eigentlich überhaut in einer einzigen Abfrage? Oder muss ich mit einer temporären Tabelle arbeiten?</p> <p>Vielen Dank schon mal,<br> paulbrause</p> https://forum.selfhtml.org/self/2008/may/7/subquery-limit/1242663?srt=yes#m1242663 Ilja Lorfniduil@gmx.de 2008-05-07T14:50:22Z 2008-05-07T14:50:22Z SUBQUERY & LIMIT <p>yo,</p> <blockquote> <p>LIMIT darf man wohl nicht in einem Subquery verwenden... :(</p> </blockquote> <p>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.</p> <p>Ilja</p> https://forum.selfhtml.org/self/2008/may/7/subquery-limit/1242662?srt=yes#m1242662 globe selfhtml@rodneyrehm.de http://rodneyrehm.de 2008-05-07T17:17:59Z 2008-05-07T17:17:59Z SUBQUERY & LIMIT <p>n'abend,</p> <blockquote> <p>Nun möchte ich allerdings für jeden Mitspieler die Punkte der letzten 3 Runden, die dieser mitgespielt hat, zusammenaddiert anzeigen.</p> </blockquote> <p>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.</p> <blockquote> <p>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).</p> </blockquote> <p>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.</p> <p>Wir brauchen das letzte Spiel für jeden Benutzer:</p> <pre><code class="block language-sql"><span class="token keyword">SELECT</span> s<span class="token punctuation">.</span>name<span class="token punctuation">,</span> <span class="token function">MAX</span><span class="token punctuation">(</span>s<span class="token punctuation">.</span>spiel<span class="token punctuation">)</span> <span class="token keyword">FROM</span> spiele_tabelle s <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> name<span class="token punctuation">;</span> </code></pre> <p>Wir brauchen das vorletzte Spiel für jeden Benutzer:</p> <pre><code class="block language-sql"><span class="token keyword">SELECT</span> s<span class="token punctuation">.</span>name<span class="token punctuation">,</span> <span class="token function">MAX</span><span class="token punctuation">(</span>s<span class="token punctuation">.</span>spiel<span class="token punctuation">)</span> <span class="token keyword">FROM</span> spiele_tabelle s <span class="token keyword">WHERE</span> s<span class="token punctuation">.</span>spiel <span class="token operator"><</span> <span class="token punctuation">(</span> <span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>s1<span class="token punctuation">.</span>spiel<span class="token punctuation">)</span> <span class="token keyword">FROM</span> spiele_tabelle s1 <span class="token keyword">WHERE</span> s1<span class="token punctuation">.</span>name <span class="token operator">=</span> s<span class="token punctuation">.</span>name <span class="token punctuation">)</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> name<span class="token punctuation">;</span> </code></pre> <p>Wir brauchen das vorvorletzte Spiel für jeden Benutzer:</p> <pre><code class="block language-sql"><span class="token keyword">SELECT</span> s<span class="token punctuation">.</span>name<span class="token punctuation">,</span> <span class="token function">MAX</span><span class="token punctuation">(</span>s<span class="token punctuation">.</span>spiel<span class="token punctuation">)</span> <span class="token keyword">FROM</span> spiele_tabelle s <span class="token keyword">WHERE</span> s<span class="token punctuation">.</span>spiel <span class="token operator"><</span> <span class="token punctuation">(</span> <span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>s1<span class="token punctuation">.</span>spiel<span class="token punctuation">)</span> <span class="token keyword">FROM</span> spiele_tabelle s1 <span class="token keyword">WHERE</span> s1<span class="token punctuation">.</span>name <span class="token operator">=</span> s<span class="token punctuation">.</span>name <span class="token operator">AND</span> s1<span class="token punctuation">.</span>spiel <span class="token operator"><</span> <span class="token punctuation">(</span> <span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>s2<span class="token punctuation">.</span>spiel<span class="token punctuation">)</span> <span class="token keyword">FROM</span> spiele_tabelle s2 <span class="token keyword">WHERE</span> s2<span class="token punctuation">.</span>name <span class="token operator">=</span> s<span class="token punctuation">.</span>name <span class="token punctuation">)</span> <span class="token punctuation">)</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> name<span class="token punctuation">;</span> </code></pre> <p>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 <a href="http://dev.mysql.com/doc/refman/5.0/en/union.html" rel="nofollow noopener noreferrer">UNION</a> zusammenfassen. Damit hätten wir dann die letzten drei Spiele eines jeden Spielers ermittelt. Bleibt also nur noch die Bildung der Summe.</p> <pre><code class="block language-sql"><span class="token keyword">SELECT</span> s<span class="token punctuation">.</span>name<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>s<span class="token punctuation">.</span>punkte<span class="token punctuation">)</span> <span class="token keyword">AS</span> punkte <span class="token keyword">FROM</span> spiele_tabelle s <span class="token keyword">JOIN</span> <span class="token punctuation">(</span> <span class="token keyword">SELECT</span> s<span class="token punctuation">.</span>name<span class="token punctuation">,</span> <span class="token function">MAX</span><span class="token punctuation">(</span>s<span class="token punctuation">.</span>spiel<span class="token punctuation">)</span> <span class="token keyword">FROM</span> spiele_tabelle s <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> name <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> s<span class="token punctuation">.</span>name<span class="token punctuation">,</span> <span class="token function">MAX</span><span class="token punctuation">(</span>s<span class="token punctuation">.</span>spiel<span class="token punctuation">)</span> <span class="token keyword">FROM</span> spiele_tabelle s <span class="token keyword">WHERE</span> s<span class="token punctuation">.</span>spiel <span class="token operator"><</span> <span class="token punctuation">(</span> <span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>s1<span class="token punctuation">.</span>spiel<span class="token punctuation">)</span> <span class="token keyword">FROM</span> spiele_tabelle s1 <span class="token keyword">WHERE</span> s1<span class="token punctuation">.</span>name <span class="token operator">=</span> s<span class="token punctuation">.</span>name <span class="token punctuation">)</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> name <span class="token keyword">UNION</span> <span class="token keyword">SELECT</span> s<span class="token punctuation">.</span>name<span class="token punctuation">,</span> <span class="token function">MAX</span><span class="token punctuation">(</span>s<span class="token punctuation">.</span>spiel<span class="token punctuation">)</span> <span class="token keyword">FROM</span> spiele_tabelle s <span class="token keyword">WHERE</span> s<span class="token punctuation">.</span>spiel <span class="token operator"><</span> <span class="token punctuation">(</span> <span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>s1<span class="token punctuation">.</span>spiel<span class="token punctuation">)</span> <span class="token keyword">FROM</span> spiele_tabelle s1 <span class="token keyword">WHERE</span> s1<span class="token punctuation">.</span>name <span class="token operator">=</span> s<span class="token punctuation">.</span>name <span class="token operator">AND</span> s1<span class="token punctuation">.</span>spiel <span class="token operator"><</span> <span class="token punctuation">(</span> <span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>s2<span class="token punctuation">.</span>spiel<span class="token punctuation">)</span> <span class="token keyword">FROM</span> spiele_tabelle s2 <span class="token keyword">WHERE</span> s2<span class="token punctuation">.</span>name <span class="token operator">=</span> s<span class="token punctuation">.</span>name <span class="token punctuation">)</span> <span class="token punctuation">)</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> name <span class="token punctuation">)</span> <span class="token keyword">AS</span> t <span class="token keyword">ON</span> <span class="token punctuation">(</span> t<span class="token punctuation">.</span>name <span class="token operator">=</span> s<span class="token punctuation">.</span>name <span class="token operator">AND</span> t<span class="token punctuation">.</span>spiel <span class="token operator">=</span> s<span class="token punctuation">.</span>spiel <span class="token punctuation">)</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> s<span class="token punctuation">.</span>name<span class="token punctuation">;</span> </code></pre> <p>Natürlich ist diese Query frei heruntergeschrieben und nicht getestet.</p> <p>weiterhin schönen abend...</p> <div class="signature">-- <br> #selfhtml hat ein Forum?<br> sh:( fo:# ch:# rl:| br:> n4:& ie:{ mo:} va:) de:] zu:} fl:( ss:? ls:[ js:| </div> https://forum.selfhtml.org/self/2008/may/7/subquery-limit/1242667?srt=yes#m1242667 globe selfhtml@rodneyrehm.de http://rodneyrehm.de 2008-05-07T16:10:53Z 2008-05-07T16:10:53Z SUBQUERY & LIMIT <p>n'abend,</p> <blockquote> <blockquote> <p>LIMIT darf man wohl nicht in einem Subquery verwenden... :(</p> </blockquote> </blockquote> <p><a href="http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html" rel="nofollow noopener noreferrer">Korrekt</a>.</p> <blockquote> <p>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.</p> </blockquote> <p>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 <a href="http://dev.mysql.com/doc/refman/5.0/en/unnamed-views.html" rel="nofollow noopener noreferrer">unbenannte Views</a>). Das ist für mich nur all zu oft der Lebensretter, wenn ich mal wieder nicht um eine LIMIT-Subquery herumkomme.</p> <p>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.</p> <p>weiterhin schönen abend...</p> <div class="signature">-- <br> #selfhtml hat ein Forum?<br> sh:( fo:# ch:# rl:| br:> n4:& ie:{ mo:} va:) de:] zu:} fl:( ss:? ls:[ js:| </div> https://forum.selfhtml.org/self/2008/may/7/subquery-limit/1242664?srt=yes#m1242664 paulbrause paulbrause@googlemail.com 2008-05-07T16:13:30Z 2008-05-07T16:13:30Z SUBQUERY & LIMIT <p>Hallo!</p> <p>Danke erstmal für die flotte Antwort, allerdings hilft's mir bei meinem Problem wohl zunächst nicht.</p> <p>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:</p> <pre><code class="block"> 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 </code></pre> <p>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?!?</p> <p>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?!?</p> <p>Oder hab ich da ein Verständnisproblem?</p> <p>Danke,<br> paulbrause</p> https://forum.selfhtml.org/self/2008/may/7/subquery-limit/1242665?srt=yes#m1242665 globe selfhtml@rodneyrehm.de http://rodneyrehm.de 2008-05-07T17:05:35Z 2008-05-07T17:05:35Z SUBQUERY & LIMIT <p>n'abend,</p> <p>ich greife meiner anderen Antwort mal vorweg:</p> <blockquote> <pre><code class="block language-sql"></code></pre> </blockquote> <blockquote> <p>SELECT t.benutzer, SUM(t.punkte) AS punkte<br> FROM test t<br> JOIN<br> (<br>   SELECT q.spiel<br>   FROM test q<br>   WHERE q.benutzer = t.benutzer<br>   ORDER BY q.spiel<br>   LIMIT 3<br> ) AS s<br> WHERE t.spiel = s.spiel<br> GROUP BY t.benutzer<br> ORDER BY punkte DESC</p> </blockquote> <pre><code class="block"> > > 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:| </code></pre> https://forum.selfhtml.org/self/2008/may/7/subquery-limit/1242666?srt=yes#m1242666 paulbrause paulbrause@googlemail.com 2008-05-07T17:12:10Z 2008-05-07T17:12:10Z SUBQUERY & LIMIT <blockquote> <p>Diese unbenannten Views werden VOR dem JOIN ausgeführt, was bedeutet, dass wir da nicht korrelierend drauf agieren können.</p> </blockquote> <p>Klar, hatte ich nicht bedacht...</p> <blockquote> <p>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...</p> </blockquote> <p>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</p> <blockquote> <p>weiterhin schönen abend...</p> </blockquote> <p>Dir auch & vielen Dank für die Hilfe...</p> https://forum.selfhtml.org/self/2008/may/7/subquery-limit/1242668?srt=yes#m1242668 dedlfix 2008-05-07T17:55:12Z 2008-05-07T17:55:12Z SUBQUERY & LIMIT <p>echo $begrüßung;</p> <blockquote> <p>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.</p> </blockquote> <p>Interessant wäre es zu wissen, wo die Subquery vorher stand, bevor du sie in den FROM-Teil umgezogen hattest.</p> <p>echo "$verabschiedung $name";</p>