在 GROUP BY 中选择了哪一行? [英] Which row is selected in GROUP BY?

问题描述

假设我有一张桌子

+------+---------+--------+
| lang | title   | url    |
+------+---------+--------+
| pt   | Livro 1 | o294jl |
| en   | Book 1  | o294jl |
| en   | Book 2  | o294jl |
+------+---------+--------+

然后我运行一个查询

SELECT lang, title
FROM table
GROUP BY url

查询结果不明显，因为lang和title的值在组内不同.

The result of the query is not obvious because the values of lang and title are different among the group.

SQL 引擎如何选择从组中返回哪一行?在我的示例中必须选择哪一行?SQL标准中是否有规定?

How does an SQL engine choose which row to return from a group? Which row must be selected in my example? Is it specified in the SQL standard?

推荐答案

从每个组的任意匹配行中选择值.对于不同的运行，这些值可能来自不同的行.理论上，同一个 SELECT 中的不同列可能来自不同的行.

Values are chosen from arbitrary matching rows for each group. The values could come from different rows for different runs. In theory, different columns in the same SELECT could come from different rows.

文档解释了这一点:

如果 ONLY_FULL_GROUP_BY 被禁用，一个 MySQL 扩展到标准GROUP BY 的 SQL 使用允许选择列表、HAVING 条件，或ORDER BY 列表引用非聚合列，即使列在功能上不依赖于 GROUP BY 列....在这种情况下，服务器可以自由地从每个组中选择任何值，因此除非它们相同，否则选择的值是不确定的，这可能不是你想要.

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. . . . 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.

您应该阅读完整的文档 关于这个主题.

You should read the complete documentation on the subject.

请注意，MySQL 现在的默认行为是拒绝此类查询.耶！

Note that the default behavior of MySQL is now to reject such queries. Yay!

这篇关于在 GROUP BY 中选择了哪一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！