MySQL - 选择不在分组依据中的列

Question

我正在尝试向现有应用程序添加功能，并且遇到了类似这样的 MySQL View :

SELECT
     AVG(table_name.col1),
     AVG(table_name.col2),
     AVG(table_name.col3),
     table_name.personID,
     table_name.col4
FROM table_name
GROUP BY table_name.personID;

好的，有一些聚合函数。您可以选择 personID，因为您是按它进行分组的。但它也选择了不在聚合函数中且不是 GROUP BY 子句一部分的列。这怎么可能？？？它是否只是选择一个随机值，因为每个组的值肯定不是唯一的？

从我的来源(MSSQL Server)来看，这是一个错误。有人可以向我解释这种行为以及为什么 MySQL 允许这种行为吗？

Answer 1

确实，此功能允许一些不明确的查询，并以静默方式返回一个结果集，其中包含从该列中选取的任意值。实际上，它往往是首先物理存储的组内行的值。

如果您仅选择功能上依赖于 GROUP BY 条件中的列的列，则这些查询不会含糊不清。换句话说，如果定义组的每个值的“不明确”列只能有一个不同的值，则没有问题。此查询在 Microsoft SQL Server(和 ANSI SQL)中是非法的，即使它在逻辑上不会导致歧义:

SELECT AVG(table1.col1), table1.personID, persons.col4
FROM table1 JOIN persons ON (table1.personID = persons.id)
GROUP BY table1.personID;

此外，MySQL 有一个 SQL 模式，使其按照标准运行:ONLY_FULL_GROUP_BY

FWIW，SQLite 也允许这些不明确的 GROUP BY 子句，但它从组中的最后行中选择值。^†

<小时/>

^† 至少在我测试的版本中是这样。 任意的意思是MySQL或SQLite将来可能会改变它们的实现，并有一些不同的行为。因此，在此类模棱两可的情况下，您不应该依赖当前的行为。最好将查询重写为确定性且不含糊的。这就是 MySQL 5.7 现在默认启用 ONLY_FULL_GROUP_BY 的原因。