SQL vs MySQL: Regeln über Aggregat operations und GROUP BY
In dieses Buch ich bin derzeit zu Lesen, während im Anschluss ein Kurs auf Datenbanken das folgende Beispiel einer illegalen Abfrage mit einem aggregate-operator ist gegeben:
Finden Sie die Namen und das Alter der älteste Seemann.
Betrachten Sie den folgenden Versuch, diese zu beantworten-Abfrage:
SELECT S.sname, MAX(S.age) FROM Sailors S
Die Absicht für diese Abfrage nicht nur das maximale Alter, aber
auch die Namen der Segler mit dem Alter. Allerdings ist diese Abfrage
illegal in SQL--wenn der SELECT-Klausel verwendet eine Aggregat-Betrieb, dann
es muss nur Aggregat-Operationen, es sei denn, die Abfrage enthält eine GROUP BY-Klausel!
Einige Zeit später, während Sie eine übung mit MySQL, ich Stand vor einem ähnlichen problem und einen Fehler gemacht hat, ähnlich wie der erwähnte. Aber MySQL nicht beschweren und Spucke einige Tabellen, die später stellte sich heraus, nicht das zu sein, was ich brauchte.
Ist die obige Abfrage eigentlich illegal in SQL, aber legal in MySQL, und wenn ja, warum ist das so?
In welcher situation würde man brauchen, um eine solche Abfrage?
Weitere Ausarbeitung der Frage:
Die Frage ist nicht, ob oder nicht alle genannten Attribute in einer SELECT-sollte auch erwähnt werden, dass in einer GRUPPE DURCH.
Es geht darum, warum die obige Abfrage unter Verwendung Attribute zusammen mit Aggregat-Operationen auf Attributen, ohne jede GRUPPE, die VON ist legal in MySQL.
Lassen Sie uns sagen, die Sailors-Tabelle sah so aus:
+----------+------+
| sname | age |
+----------+------+
| John Doe | 30 |
| Jane Doe | 50 |
+----------+------+
Die Abfrage würde dann wieder:
+----------+------------+
| sname | MAX(S.age) |
+----------+------------+
| John Doe | 50 |
+----------+------------+
Nun, wer würde das brauchen? John Doe ist nicht 50, er ist 30!
Wie bereits in dem Zitat aus dem Buch, dies ist ein Erster Versuch, um den Namen und das Alter des ältesten Segler, in diesem Beispiel, Jane Doe im Alter von 50 Jahren.
SQL würde sagen, dass diese Abfrage ist illegal, aber MySQL nur Einnahmen und spuckt "Müll".
Wer würde brauchen diese Art von Ergebnis?
Warum macht MySQL erlauben, diese kleine Falle für Neulinge?
- Sie sicher, dass das die genaue Abfrage aus dem Buch? Es gibt kein Aggregat in es.
- 1. Ja. 2. Ja. 3. das weiß Gott allein. 4. Faulheit
- Ich gehe davon aus, dass der Abfrage ist es, zu geben, die grundlegenden schema, mit zu arbeiten.
- Stellen Sie sich die situation, entwickeln eine Menge von Abfragen in MySql mithilfe dieser syntax dann vorstellen, das Szenario, wo Sie haben sich zu bewegen, diese Abfragen an SQL-Server... müsste man ändern alle Ihre Anfragen, die diese verwendet. Wenn, jedoch, Sie würde die ANSI-syntax in den ersten Platz, dann wäre dies nicht (unbedingt) der Fall sein.
- Hoppla, du hast Recht, ich habe vergessen zu schreiben, MAX (S. Alter), es ist nun behoben. =)
- Klar, es gibt zwei verschiedene "Probleme" hier - 1) die Tatsache, dass keine
GROUP BY
- Klausel wird angegeben, an allen 2) Die Tatsache, dass Sie eine nicht-Aggregat-Spalte aufgelistet (der name der Seemann). Welches sind Sie tatsächlich besorgt um. - Dies könnte helfen, das Verständnis der seltsame MySQL-Verhalten: rpbouman.blogspot.de/2007/05/debunking-group-by-myths.html
- Realted Frage: Warum hat MySQL ein feature hinzufügen, dass Konflikte mit SQL-standards?
- Wir können bestätigen, dass die Abfrage sollte illegal sein. Entweder eine weitere Zeile "GROUP BY sname" sollte Hinzugefügt werden, oder "sname" sollte nicht erwähnt werden, dass in der SELECT-Anweisung. Wie ich es sehe, nach SQL, nicht-Aggregat-Spalten und GROUP BY sollte "gehen hand-in-hand". =)
- Scheint so, ich check it out. Thx!!! ^_^
- Eine interessante Lektüre, Es scheint, es hat alle Antworten, thx!!! Ich würde vorschlagen, du postest es als Antwort statt eines Kommentars. ^_^
Du musst angemeldet sein, um einen Kommentar abzugeben.
Durch die Art und Weise, es ist die Standard-MySQL-Verhalten. Aber es kann verändert werden, indem Sie ONLY_FULL_GROUP_BY server-Modus in der
my.ini
- Datei oder in der Sitzung -ONLY_FULL_GROUP_BY - Erlauben es nicht, die Abfragen für die select-Liste bezieht sich auf nonaggregated Spalten, die nicht mit Namen in der GROUP BY-Klausel.
Ja
Ich weiß nicht, die Gründe für die design-Entscheidungen, die in MySQL, aber in Anbetracht, dass Sie den tatsächlichen Zusammenhang der Daten aus den gleichen Zeile(N) als das Aggregat stammt (z.B.
MAX
oderMIN
) mit nur etwas mehr Arbeit, ich sehe keinen Vorteil in der Rückkehr zusätzlichen Spalte die Daten aus beliebigen Zeilen.Ich eine starke Abneigung gegen dieses "feature" in MySQL und es Reisen viele Menschen lernen Aggregate auf MySQL und dann wechseln zu einem anderen dbms, und plötzlich erkennen, dass Sie nie so Recht wussten, was Sie Taten.
Basierend auf ein link die a_horse_with_no_name in einem Kommentar, ich bin angekommen in meiner eigenen Antwort:
Es scheint, dass die MySQL-Möglichkeit der Verwendung von GROUP BY unterscheidet sich von der SQL Weg, um zu ermöglichen, weglassen von Spalten, die von der GROUP BY-Klausel, wenn Sie sind funktional abhängig von anderen Spalten enthalten sowieso.
Können sagen, wir haben eine Tabelle anzeigen, die die Aktivität von einem bank-Konto.
Es ist nicht sehr durchdacht Tabelle, aber es ist die einzige, die wir haben, und das zu tun haben.
Stattdessen verfolgen Sie einen Betrag, wir stellen uns vor, ein Konto beginnt bei '0' und alle Transaktionen aufgezeichnet wird, statt, so dass die Menge ist die Summe der Transaktionen. Die Tabelle könnte wie folgt Aussehen:
Es ist klar, dass der 'name' ist funktional abhängig von der "costumerID'.
(Andersrum wäre auch möglich, in diesem Beispiel.)
Was, wenn wir wollten, um zu wissen, die costumerID, Namen und die aktuelle Höhe der einzelnen Kunden?
In einer solchen situation, zwei sehr ähnliche Abfragen zurückgeben würde das folgende Ergebnis:
Diese Abfrage kann ausgeführt werden in MySQL, und ist legal nach SQL.
Diese Abfrage kann ausgeführt werden in MySQL, und ist NICHT rechtliche nach SQL.
Die folgende Zeile würde die Abfrage und Fehlermeldung statt, da wäre es nun an die SQL-Möglichkeit der Verwendung von aggregation operations und GROUP BY:
Argument für die Zulassung der zweiten Abfrage in MySQL zu sein scheint, dass es wird angenommen, dass alle Spalten erwähnt WÄHLEN, aber nicht erwähnt, in der GRUPPE DURCH, sind entweder in einer Aggregat-Betrieb, (der Fall mit der 'Transaktion'), oder sind funktional abhängig von anderen Spalten enthalten, (der Fall mit 'name'). Bei 'name' können wir sicher sein, dass der richtige "name" wird gewählt für die Gruppe alle Einträge, da es funktional abhängig von 'costumerID", und deshalb gibt es nur eine eventuell-Namen für jede Gruppe von costumerID ist.
Diese Art der Verwendung von GROUP BY-scheint fehlerhaft zäh sein, da es nicht, jede weitere Prüfung auf das, was übrig ist von der GROUP BY-Klausel. Menschen können wählen, und wählen Sie die Spalten aus Ihrer SELECT-Anweisung in den GROUP BY-Klausel, wie Sie sehen, passen, auch wenn es keinen Sinn macht, zu zählen oder weglassen einer bestimmten Spalte.
Sailor Beispiel veranschaulicht dieses Manko sehr gut.
Bei der Verwendung von Aggregations-Operatoren (ggf. in Verbindung mit GROUP BY), jede Gruppe Eintrag im zurückgegebenen set hat nur einen Wert für die einzelnen Spalten. Im Fall von Seeleuten, da die GROUP BY-Klausel ausgelassen wird, wird die gesamte Tabelle in einem einzigen Eintrag Gruppe. Dieser Eintrag muss einen Namen und ein Höchstalter. Die Wahl eines maximalen Alters für diesen Eintrag ist ein no-brainer, da MAX(S. Alter) nur einen Wert zurückgibt. Im Fall von S. sname obwohl, wich ist nur erwähnt in WÄHLEN, es gibt jetzt so viele Möglichkeiten wie es gibt eindeutige sname ' s in der ganzen Sailor Tabelle (in diesem Fall zwei, John und Jane Doe). MySQL doens nicht alle Ahnung, was zu wählen, hatten wir es nicht alle, und es nicht auf die Bremsen in der Zeit, so muss es nur abholen, was auch immer zuerst kommt (Jane Doe). Wenn die beiden Zeilen vertauscht, würde es tatsächlich geben "die richtige Antwort" durch Zufall. Es scheint nur schlicht dumm, daß so etwas erlaubt ist in MySQL, dass das Ergebnis einer Abfrage mit GROUP BY könnte potententially hängt von der Sortierung der Tabelle, wenn etwas übrig bleibt in der GROUP BY-Klausel. Offenbar, das ist genau wie MySQL-Rollen. Aber trotzdem hätte man nicht wenigstens die Höflichkeit warnt uns, wenn er keine Ahnung hat, was es tut, weil der eine "fehlerhafte" Abfrage? Ich meine, sicher, wenn Sie die falschen Anweisungen, um ein Programm, würde es vermutlich nicht (oder sollte nicht) tun, wie Sie wollen, aber wenn Sie geben unklare Anweisungen, ich würde sicherlich nicht wollen, dass es nur beginnen zu raten oder alles auswählen, was zuerst kommt... -_-'
MySQL ermöglicht diese nicht-standard-SQL-syntax, da gibt es zumindest einen konkreten Fall, in dem es macht die SQL nominell einfacher zu schreiben. Diesem Fall ist, wenn man das verbinden von zwei Tabellen, die einen PRIMÄR - /Fremdschlüssel-Beziehung (ob erzwungen durch die Datenbank oder nicht), und wollen Sie einen aggregierten Wert aus der FOREIGN KEY-Seite und vielen Spalten aus dem Primärschlüssel Seite.
Betrachten Sie nun ein system mit
Customer
undOrders
Tabellen. Stellen Sie sich vor Sie wollen alle die Felder aus der Kunden-Tabelle zusammen mit der Summe derAmount
Feld aus derOrders
Tabelle. In standard-SQL würden Sie schreiben:Beachten Sie die unhandlichen GROUP BY-Klausel, und sich vorstellen, was es Aussehen würde, wenn es mehr Spalten, die Sie wollten von Kunden.
In MySQL, könnten Sie schreiben:
oder sogar (ich glaube, ich habe es nicht ausprobiert):
Viel einfacher zu schreiben. In diesem speziellen Fall es ist auch sicher, da Sie wissen, dass nur eine Zeile aus der
Customer
Tabelle wird dazu beitragen, jede Gruppe (vorausgesetzt, CustomerID PRIMARY-oder UNIQUE-SCHLÜSSEL).Ich persönlich bin kein großer fan von dieser Ausnahme zu standard-SQL-syntax (da gibt es viele Fälle, in denen es nicht sicher diese syntax verwenden und verlassen sich auf immer Werte aus einer bestimmten Zeile in der Gruppe), aber ich kann sehen, wo es bestimmte Arten von Abfragen einfacher und (im Fall meiner zweiten MySQL-Beispiel) möglich.