MySQL对GROUP BY的处理
SQL-92和更早版本不允许选择列表,HAVING
条件或ORDER BY
列表引用未在GROUP BY
子句中命名的未聚合列的查询。例如,此查询在标准SQL-92中是非法的,因为name
选择列表中的未聚合列未出现在中GROUP BY
:
SELECT o.custid, c.name , MAX(o.payment)FROM ordersAS o, customersAS cWHERE o.custid = c.custidGROUP BY o.custid;
为了使查询在SQL-92中合法,name
必须从选择列表中省略该列或在GROUP BY
子句中命名该列。
SQL:1999和更高版本允许这些非聚合,如果它们在功能上依赖于GROUP BY
列,则每个可选功能T301都允许此类非聚合:如果name
和之间存在这种关系custid
,则查询合法。例如,如果是custid
的主键就是这种情况customers
。
MySQL实现对功能依赖性的检测。如果ONLY_FULL_GROUP_BY
启用了 SQL模式(默认情况下为SQL模式),则MySQL拒绝查询其选择列表,HAVING
条件或ORDER BY
列表引用GROUP BY
未在该子句中命名或在功能上不依赖于它们的未聚合列的查询。
启用GROUP BY
SQL ONLY_FULL_GROUP_BY
模式时, MySQL还允许在子句中未命名的非聚合列,但前提是该列限于单个值,如以下示例所示:
mysql>CREATE TABLE mytable ( -> id INTUNSIGNED NOT NULLPRIMARY KEY , -> a VARCHAR(10), -> b INT -> ); mysql>INSERT INTO mytable ->VALUES (1, 'abc', 1000), -> (2, 'abc', 2000), -> (3, 'def', 4000); mysql>SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY'); mysql>SELECT a, SUM(b)FROM mytableWHERE a = 'abc'; +------ +-------- + | a | SUM(b) | +------ +-------- + | abc | 3000 | +------ +-------- +
也可以在有一个以上的非聚合列SELECT
使用时列表ONLY_FULL_GROUP_BY
。在这种情况下,必须在WHERE
子句中将每个这样的列限制为一个值,并且所有这样的限制条件必须通过逻辑连接AND
,如下所示:
mysql>DROP TABLE IF EXISTS mytable; mysql>CREATE TABLE mytable ( -> id INTUNSIGNED NOT NULLPRIMARY KEY , -> a VARCHAR(10), -> b VARCHAR(10), -> c INT -> ); mysql>INSERT INTO mytable ->VALUES (1, 'abc', 'qrs', 1000), -> (2, 'abc', 'tuv', 2000), -> (3, 'def', 'qrs', 4000), -> (4, 'def', 'tuv', 8000), -> (5, 'abc', 'qrs', 16000), -> (6, 'def', 'tuv', 32000); mysql>SELECT @@session.sql_mode; +--------------------------------------------------------------- + | @@session.sql_mode | +--------------------------------------------------------------- + | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +--------------------------------------------------------------- + mysql>SELECT a, b, SUM(c)FROM mytable ->WHERE a = 'abc' AND b = 'qrs'; +------ +------ +-------- + | a | b | SUM(c) | +------ +------ +-------- + | abc | qrs | 17000 | +------ +------ +-------- +
如果ONLY_FULL_GROUP_BY
被禁用,则对标准SQL使用的MySQL扩展GROUP BY
允许选择列表,HAVING
条件或ORDER BY
列表引用未聚合的列,即使这些列在功能上不依赖于GROUP BY
列。这导致MySQL接受前面的查询。在这种情况下,服务器可以从每个组中自由选择任何值,因此,除非它们相同,否则选择的值是不确定的,这可能不是您想要的。此外,通过添加ORDER BY
子句不能影响从每个组中选择值。选择值后进行结果集排序,然后ORDER BY
不会影响服务器在每个组中选择哪个值。禁用ONLY_FULL_GROUP_BY
主要有用,因为您知道由于数据的某些属性,每个未聚合列中未命名的所有值GROUP BY
对于每个组都是相同的。
ONLY_FULL_GROUP_BY
通过使用ANY_VALUE()
引用未聚合的列,可以在不禁用的情况下实现相同的效果。
下面的讨论演示了功能依赖性,不存在功能依赖性时MySQL产生的错误消息,以及在没有功能依赖性的情况下使MySQL接受查询的方法。
此查询ONLY_FULL_GROUP_BY
启用后可能无效,因为子句中address
未命名选择列表中的未聚合列GROUP BY
:
SELECT name , address, MAX(age)FROM tGROUP BY name ;
如果name
是的主键t
或是唯一NOT NULL
列,则查询有效。在这种情况下,MySQL会认识到所选列在功能上取决于分组列。例如,如果name
是主键,则其值确定的值,address
因为每个组只有主键的一个值,因此只有一行。结果,address
在组中选择值时没有随机性,也不需要拒绝查询。
如果name
不是主键t
或唯一NOT NULL
列,则查询无效。在这种情况下,无法推断出功能依赖性,并且会发生错误:
mysql>SELECT name , address, MAX(age)FROM tGROUP BY name ; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.t.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
如果您知道,对于给定的数据集,每个name
值实际上唯一地确定该address
值,address
实际上在功能上取决于name
。要告诉MySQL接受查询,可以使用以下ANY_VALUE()
函数:
SELECT name , ANY_VALUE(address), MAX(age)FROM tGROUP BY name ;
或者,禁用ONLY_FULL_GROUP_BY
。
但是,前面的示例非常简单。特别是,不太可能将您分组在一个主键列上,因为每个组将只包含一行。有关在更复杂的查询中展示功能依赖关系的其他示例,请参见“检测功能依赖关系”。
如果查询中聚合函数和没有GROUP BY
条款,也不能有非聚合列在选择列表,HAVING
条件,或ORDER BY
清单,ONLY_FULL_GROUP_BY
启用:
mysql>SELECT name , MAX(age)FROM t; ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'mydb.t.name'; this is incompatible with sql_mode=only_full_group_by
如果没有GROUP BY
,则只有一个组,并且不确定name
为该组选择哪个值。ANY_VALUE()
如果name
MySQL选择哪个值无关紧要,也可以在这里使用:
SELECT ANY_VALUE(name ), MAX(age)FROM t;
ONLY_FULL_GROUP_BY
也影响使用DISTINCT
和的查询的处理ORDER BY
。考虑表的情况下,t
有三列c1
,c2
以及c3
包含这些行:
c1 c2 c3 1 2 A 3 4 B 1 2 C
假设我们执行以下查询,期望结果按以下顺序排序c3
:
SELECT DISTINCT c1, c2FROM tORDER BY c3;
为了订购结果,必须首先消除重复。但是要这样做,我们应该保留第一行还是第三行?这种任意选择会影响的保留值c3
,进而影响顺序并使其也具有任意性。为避免此问题,如果任何表达式不满足以下条件之一,则具有DISTINCT
且ORDER BY
被拒绝为无效的查询ORDER BY
:
- 该表达式等于选择列表中的一个
- 表达式引用的并属于查询的所选表的所有列都是选择列表的元素
对标准SQL的另一个MySQL扩展允许在HAVING
子句中引用选择列表中的别名表达式。例如,以下查询返回name
在表中仅出现一次的值orders
:
SELECT name , COUNT(name )FROM ordersGROUP BY name HAVING COUNT(name ) = 1;
MySQL扩展允许在HAVING
子句中为聚合列使用别名:
SELECT name , COUNT(name )AS cFROM ordersGROUP BY name HAVING c = 1;
标准SQL在GROUP BY
子句中仅允许使用列表达式,因此诸如此类的语句无效,因为FLOOR(value/100)
它是非列表达式:
SELECT id, FLOOR(value /100)FROM tbl_nameGROUP BY id, FLOOR(value /100);
MySQL扩展了标准SQL,以允许GROUP BY
子句中使用非列表达式,并认为前面的语句有效。
标准SQL也不允许在GROUP BY
子句中使用别名。MySQL扩展了标准SQL以允许使用别名,因此另一种编写查询的方法如下:
SELECT id, FLOOR(value /100)AS valFROM tbl_nameGROUP BY id, val;
val
在GROUP BY
子句中,别名被视为列表达式。
在GROUP BY
子句中存在非列表达式时,MySQL会识别该表达式与选择列表中的表达式之间的相等性。这意味着在ONLY_FULL_GROUP_BY
启用SQL模式的情况下,包含的查询GROUP BY id, FLOOR(value/100)
是有效的,因为FLOOR()
在选择列表中会出现相同的表达式。但是,MySQL不会尝试识别对非GROUP BY
列表达式的功能依赖性,因此ONLY_FULL_GROUP_BY
即使第三个选择的表达式是该id
列和子句中的FLOOR()
表达式的简单公式,以下查询在启用后也是无效的GROUP BY
:
SELECT id, FLOOR(value /100), id+FLOOR(value /100)FROM tbl_nameGROUP BY id, FLOOR(value /100);
解决方法是使用派生表:
SELECT id, F, id+FFROM (SELECT id, FLOOR(value /100)AS FFROM tbl_nameGROUP BY id, FLOOR(value /100))AS dt;