MySQL - 选择不在分组中的列 [英] MySQL - Selecting a Column not in Group By

问题描述

我试图向一个已存在的应用程序添加功能，并且遇到了一个如下所示的MySQL视图：

  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？

解决方案

确实，这个功能允许一些模糊的查询，默默地返回一个结果集，其中包含从该列中选取的任意值。在实践中，它往往是来自组内行内的物理存储的值。

如果您只选择功能上的列，这些查询并不明确取决于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子句，但是它从组中的 last 行中选择了值。

---

^†至少在我测试的版本中。什么意思是任意的是MySQL或SQLite可以在将来改变它们的实现，并且有一些不同的行为。因此，你不应该依赖于他们目前处于这种模棱两可的情况下的行为方式。重写你的查询是确定性的而不是含糊不清的。这就是为什么MySQL 5.7现在默认启用ONLY_FULL_GROUP_BY的原因。

I'm trying to add features to a preexisting application and I came across a MySQL view something like this:

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;

OK so there's a few aggregate functions. You can select personID because you're grouping by it. But it also is selecting a column that is not in an aggregate function and is not a part of the GROUP BY clause. How is this possible??? Does it just pick a random value because the values definitely aren't unique per group?

Where I come from (MSSQL Server), that's an error. Can someone explain this behavior to me and why it's allowed in MySQL?

解决方案

It's true that this feature permits some ambiguous queries, and silently returns a result set with an arbitrary value picked from that column. In practice, it tends to be the value from the row within the group that is physically stored first.

These queries aren't ambiguous if you only choose columns that are functionally dependent on the column(s) in the GROUP BY criteria. In other words, if there can be only one distinct value of the "ambiguous" column per value that defines the group, there's no problem. This query would be illegal in Microsoft SQL Server (and ANSI SQL), even though it cannot logically result in ambiguity:

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

Also, MySQL has an SQL mode to make it behave per the standard: ONLY_FULL_GROUP_BY

FWIW, SQLite also permits these ambiguous GROUP BY clauses, but it chooses the value from the last row in the group.^†

---

^† At least in the version I tested. What it means to be arbitrary is that either MySQL or SQLite could change their implementation in the future, and have some different behavior. You should therefore not rely on the behavior staying they way it is currently in ambiguous cases like this. It's better to rewrite your queries to be deterministic and not ambiguous. That's why MySQL 5.7 now enables ONLY_FULL_GROUP_BY by default.

这篇关于MySQL - 选择不在分组中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！