MySQL对Goup By的处理

    在SQL-92以及更早的SQL语言规范中并不允许Select中的字段、HAVING中的条件或者Order by 中的字段使用没有出现在GROUP BY中的非聚合列，例如，这个例子在标准的SQL-92规范中是不合法的，因为select中使用的name列，而这个没有参与聚合操作的列并未出现在Group by 中；

SELECT o.custid, c.name, MAX(o.payment) 
FROM orders AS o, customers AS c WHERE o.custid = c.custid GROUP BY o.custid;

更正方法是去掉name或者把它加到group by中；

    但是根据SQL:99以及之后的SQL语言规范中的可选特性T301，如果这些列函数依赖(参考文末解释)于GROUP BY 中的列时是允许这么写的：即如果列name和custid之间存在这种关系的话，上面的SQL就是合法的，比如当custid是customers表的主键的时候；

    MySQL实现了对函数依赖关系的探测。如果数据库启用了ONLY_FULL_GROUP_BY SQL模式(默认启用)，那么select 列表，HAVING条件或者ORDER BY 列表中一旦引用了没有进行聚合操作的列，而且这些列既没有出现在GROUP BY 条件中并且GROUP BY中的列也和这些列没有函数依赖关系MySQL是不会执行查询的；

    如果禁用了ONLY_FULL_GROUP_BY，MySQL会对标准的GROUP BY功能进行扩展，它允许select中的列，HAVING中的条件或者ORDER BY 中的列引用非聚合列，甚至当这些列与GROUP BY 中的列没有函数依赖关系时也是可以的。这样MySQL就允许上述SQL语句的写法了，这时数据库在每个分组中会自由选择这种列的值。因此，对于这个列来说除非在每个组中的值都是一样的，否则最终的值可能不是你想要的，因为这个值的选择是不确定的。更进一步来说，这些值的选择不会受ORDER BY 条件所影响，结果集的排序是在值选择后进行的，并且ORDER BY 也不会影响数据库对每个组中最终结果的选择。如果你可以确定，根据数据之间的关系，未出现在GROUP BY中的每个非聚合列中的所有值对每个组来说都是相同的，这时禁用ONLY_FULL_GROUP_BY是有用的；

      你也可以不用禁用ONLY_FULL_GROUP_BY就实现这个功能，方法是使用ANY_VALUE()函数来处理非聚合列。

下面的讨论论证函数依赖，当没有函数依赖时MySQL产生的错误信息，以及在查询中没有函数依赖时让MySQL执行这种查询的方法；

在启用ONLY_FULL_GROUP_BY时这个查询可能是非法的，因为非聚合列address出现在select列表中但是没有出现在GROUP BY 条件中，

SELECT name, address, MAX(age) FROM t GROUP BY name;

当name是t表的主键或者是一个非空且唯一的列时，这个查询就是有效的。在这种情况下，MySQL会认为address列函数依赖于用来分组的列。比如，如果name列是主键，那么它的值确定了address也就确定了，因为每个组中有且仅有一个主键值也就是一行数据。结果就是数据库在选择每个组中的address时将不会存在不确定性，数据库也没有必要拒绝这个查询了

当name不是t表的主键或者不是非空惟一列时这个查询就是无效的，在这种情况下，不存在函数依赖，并且会出现以下查询错误：

mysql> SELECT name, address, MAX(age) FROM t GROUP 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 t GROUP BY name;

另一种方式就是禁用ONLY_FULL_GROUP_BY.

然而，之前的例子是很简单的一种情况。尤其是你不太可能对一个单独的主键列分组，因为每一组只有一行数据。关于论证函数依赖的一些复杂查询例子请参考12.20.4,”函数依赖探测”

在启用了ONLY_FULL_GROUP_BY的情况下，如果一个查询有聚合函数并且没有GROUP BY 条件，那么在这个查询中，select列表，HAVING条件以及ORDER 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选择哪一个是不确定的。如果MySQL最终选择哪个name值不重要，那么你可以使用ANY_VALUE()函数.

SELECT ANY_VALUE(name), MAX(age) FROM t;

ONLY_FULL_GROUP_BY同样会影响使用DISTINCT和ORDER BY的查询。思考一下,假设有一个表t，该表有3列，c1,c2,c3，包含以下数据：

c1	c2	c3
1	2	A
3	4	B
1	2	C

假设我们执行以下查询语句，希望查询结果按照c3排序

SELECT DISTINCT c1, c2 FROM t ORDER BY c3;

为了对结果排序，重复数据得首先去掉。但是这么做话，我们要保留第一行还是第三行？随意决定保留哪个c3的值会影响到排序，而且排序也会变得不确定起来。对于有DISTINCT和ORDER BY的查询来说，如果ORDER BY 表达式不满足以下条件之一，就会认为这个查询语句是不正确的，以防止出现上述问题：

• 出现在ORDER BY表达式中的列在select列表中也可以找到；
• 所有出现在ORDER BY 表达式中且属于所查询表的列，同样要出现在SELECT列表中

比如：

SQL1(正确)

SELECT DISTINCT category, category+1,NAME+"" FROM sf_product  F ORDER BY NAME+""

  

SQL2(正确)

SELECT DISTINCT category, category+1,NAME+"" FROM sf_product  F ORDER BY NAME+"",category  

SQL3(错误)

SELECT DISTINCT category+1,NAME+"" FROM sf_product  F ORDER BY NAME+"",category

  

另一个MySQL对标准SQL的扩展是它允许在HAVING条件中使用select列表中的别名。比如，下面的查询语句，返回orders表中name只出现一次的记录:

SELECT name, COUNT(name) FROM orders  GROUP BY name HAVING COUNT(name) = 1;

MySQL的功能扩展允许在HAVING条件中对聚合列使用别名：

SELECT name, COUNT(name) AS c FROM orders GROUP BY name HAVING c = 1;

标准SQL只允许在GROUP BY条件中使用列表达式，因此类似于这种的声明是无效的，因为FLOOR(value/100)是一个非列表达式：(在Oracle中则不能这么用)

SELECT id, FLOOR(value/100) FROM tbl_name GROUP BY id, FLOOR(value/100);

MySQL的对标准SQL的功能扩展允许非列表达式出现在GROUP BY 条件中，并且认为上述SQL是正确的

标准SQL同样不允许在GROUP BY条件中使用别名，MySQL扩展了标准SQL的功能以允许这种写法，因此上述SQL也可以这么写：

SELECT id, FLOOR(value/100) AS val FROM tbl_name GROUP BY id, val;

别名val被认为是GROUP BY 条件中的列表达式。

     在GROUP BY 条件中出现的非列表达式，MySQL会认为和select列表中的是一样的。就是说启用了ONLY_FULL_GROUP_BY模式后，包含GROUP BY id, FLOOR(value/100)的查询语句就是有效的了，因为有FLOOR()同样也在select表达式中。然而，MySQL不会尝试识别GROUP BY 表达式中非列表达式中的函数依赖，因此在启用了ONLY_FULL_GROUP_BY模式后以下查询语句是无效的，即使第三个查询表达式是一个作用在id上的简单公式而已，并且FLOOR()表达式也出现在了GROUP BY条件中：

SELECT id, FLOOR(value/100), id+FLOOR(value/100) FROM tbl_name  GROUP BY id, FLOOR(value/100);

另外一个方案是使用衍生表：

SELECT id, F, id+F FROM (SELECT id, FLOOR(value/100) AS F
 FROM tbl_name  GROUP BY id, FLOOR(value/100)) AS dt;

总结：

1.在启用了ONLY_FULL_GROUP_BY模式时：

A.除非有函数依赖，否则Select、HAVING、ORDER  BY中的非聚合列必须出现在GROUP BY条件中;

B.如果没有GROUP BY 条件，Select、HAVING、ORDER  BY中不允许出现的非聚合列；

C.有DISTINCT和ORDER BY 的查询，ORDER BY 表达式要同时满足以下条件：

•    出现在ORDER BY表达式中的列在select列表中也可以找到；
•    所有出现在ORDER BY 表达式中且属于所查询表的列，同样要出现在SELECT列表中；

D.可以在GROUP BY条件中使用非列表达式；

2.禁用ONLY_FULL_GROUP_BY模式时：

A.允许select中的列，HAVING中的条件或者ORDER BY 中的列引用非聚合列，甚至当这些列与GROUP BY 中的列没有函数依赖关系时也是可以的；

B.如果不想禁用ONLY_FULL_GROUP_BY模式也实现这一功能，可以使用ANY_VALUE()函数实现;

3.MySQL允许在HAVING 和GROUP BY中使用别名；

注：

函数依赖(Functional Dependency)：

 当一个属性可以惟一地决定另一个属性时，我们称这两个属性之间存在函数依赖；如果R表示属性X与Y之间的关系，这两个属性之间的函数依赖表现为X->Y，表示Y函数依赖于X，这里X为行列集,Y为因变量。每个X值都精确的与一个Y值相关联；

  数据库中的函数依赖表示两个属性集之间的限制关系。

参考：

1.MySQL 8.0 Reference Manual

2.What does Functional Dependency mean?