Rechercher dans le manuel MySQL
12.20.3 MySQL Handling of GROUP BY
SQL-92 and earlier does not permit queries for which the select
list, HAVING
condition, or ORDER
BY
list refer to nonaggregated columns that are not
named in the GROUP BY
clause. For example,
this query is illegal in standard SQL-92 because the
nonaggregated name
column in the select list
does not appear in the GROUP BY
:
For the query to be legal in SQL-92, the name
column must be omitted from the select list or named in the
GROUP BY
clause.
SQL:1999 and later permits such nonaggregates per optional
feature T301 if they are functionally dependent on
GROUP BY
columns: If such a relationship
exists between name
and
custid
, the query is legal. This would be the
case, for example, were custid
a primary key
of customers
.
MySQL implements detection of functional dependence. If the
ONLY_FULL_GROUP_BY
SQL mode is
enabled (which it is by default), MySQL rejects queries for
which the select list, HAVING
condition, or
ORDER BY
list refer to nonaggregated columns
that are neither named in the GROUP BY
clause
nor are functionally dependent on them.
If ONLY_FULL_GROUP_BY
is
disabled, a MySQL extension to the standard SQL use of
GROUP BY
permits the select list,
HAVING
condition, or ORDER
BY
list to refer to nonaggregated columns even if the
columns are not functionally dependent on GROUP
BY
columns. This causes MySQL to accept the preceding
query. In this case, the server is free to choose any value from
each group, so unless they are the same, the values chosen are
nondeterministic, which is probably not what you want.
Furthermore, the selection of values from each group cannot be
influenced by adding an ORDER BY
clause.
Result set sorting occurs after values have been chosen, and
ORDER BY
does not affect which value within
each group the server chooses. Disabling
ONLY_FULL_GROUP_BY
is useful
primarily when you know that, due to some property of the data,
all values in each nonaggregated column not named in the
GROUP BY
are the same for each group.
You can achieve the same effect without disabling
ONLY_FULL_GROUP_BY
by using
ANY_VALUE()
to refer to the
nonaggregated column.
The following discussion demonstrates functional dependence, the error message MySQL produces when functional dependence is absent, and ways of causing MySQL to accept a query in the absence of functional dependence.
This query might be invalid with
ONLY_FULL_GROUP_BY
enabled
because the nonaggregated address
column in
the select list is not named in the GROUP BY
clause:
The query is valid if name
is a primary key
of t
or is a unique NOT
NULL
column. In such cases, MySQL recognizes that the
selected column is functionally dependent on a grouping column.
For example, if name
is a primary key, its
value determines the value of address
because
each group has only one value of the primary key and thus only
one row. As a result, there is no randomness in the choice of
address
value in a group and no need to
reject the query.
The query is invalid if name
is not a primary
key of t
or a unique NOT
NULL
column. In this case, no functional dependency
can be inferred and an error occurs:
- ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
If you know that, for a given data set,
each name
value in fact uniquely determines
the address
value, address
is effectively functionally dependent on
name
. To tell MySQL to accept the query, you
can use the ANY_VALUE()
function:
Alternatively, disable
ONLY_FULL_GROUP_BY
.
The preceding example is quite simple, however. In particular, it is unlikely you would group on a single primary key column because every group would contain only one row. For addtional examples demonstrating functional dependence in more complex queries, see Section 12.20.4, “Detection of Functional Dependence”.
If a query has aggregate functions and no GROUP
BY
clause, it cannot have nonaggregated columns in the
select list, HAVING
condition, or
ORDER BY
list with
ONLY_FULL_GROUP_BY
enabled:
- #1 of SELECT list contains nonaggregated column 'mydb.t.name'; this
Without GROUP BY
, there is a single group and
it is nondeterministic which name
value to
choose for the group. Here, too,
ANY_VALUE()
can be used, if it is
immaterial which name
value MySQL chooses:
ONLY_FULL_GROUP_BY
also affects handling of
queries that use DISTINCT
and ORDER
BY
. Consider the case of a table t
with three columns c1
, c2
,
and c3
that contains these rows:
c1 c2 c3
1 2 A
3 4 B
1 2 C
Suppose that we execute the following query, expecting the
results to be ordered by c3
:
To order the result, duplicates must be eliminated first. But to
do so, should we keep the first row or the third? This arbitrary
choice influences the retained value of c3
,
which in turn influences ordering and makes it arbitrary as
well. To prevent this problem, a query that has
DISTINCT
and ORDER BY
is
rejected as invalid if any ORDER BY
expression does not satisfy at least one of these conditions:
The expression is equal to one in the select list
All columns referenced by the expression and belonging to the query's selected tables are elements of the select list
Another MySQL extension to standard SQL permits references in
the HAVING
clause to aliased expressions in
the select list. For example, the following query returns
name
values that occur only once in table
orders
:
The MySQL extension permits the use of an alias in the
HAVING
clause for the aggregated column:
Standard SQL permits only column expressions in GROUP
BY
clauses, so a statement such as this is invalid
because FLOOR(value/100)
is a noncolumn
expression:
MySQL extends standard SQL to permit noncolumn expressions in
GROUP BY
clauses and considers the preceding
statement valid.
Standard SQL also does not permit aliases in GROUP
BY
clauses. MySQL extends standard SQL to permit
aliases, so another way to write the query is as follows:
The alias val
is considered a column
expression in the GROUP BY
clause.
In the presence of a noncolumn expression in the GROUP
BY
clause, MySQL recognizes equality between that
expression and expressions in the select list. This means that
with ONLY_FULL_GROUP_BY
SQL
mode enabled, the query containing GROUP BY id,
FLOOR(value/100)
is valid because that same
FLOOR()
expression occurs in the
select list. However, MySQL does not try to recognize functional
dependence on GROUP BY
noncolumn expressions,
so the following query is invalid with
ONLY_FULL_GROUP_BY
enabled,
even though the third selected expression is a simple formula of
the id
column and the
FLOOR()
expression in the
GROUP BY
clause:
A workaround is to use a derived table:
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-group-by-handling.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.