gpt4 book ai didi

mysql - 为什么这个 MySQL 语句中的 GROUP BY 子句不抛出错误?

转载 作者:可可西里 更新时间:2023-11-01 08:10:51 26 4
gpt4 key购买 nike

我在 here 中使用 w3s 作为示例

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders,
Orders.OrderDate
FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;

我很困惑为什么上面的语句没有抛出错误。当我们按所有 OrderID 进行聚合时,MySQL 如何知道要使用哪个 OrderDate

最佳答案

How does mysql know which OrderDate to use when we are aggregating by all OrderID?

事实并非如此。它只选择一个,因为它假定您已按所有必要的列进行分组,并且任何不在 GROUP BY 中且不受任何聚合函数约束的列都将具有相同的值对于每个组。

作为一种优化,它是一种非标准行为,允许服务器将源行中每个组中的一个值“泄漏”到结果集中,从而减少 GROUP 数据的大小BY 必须管理。 哪个源行的值用于每个组是未定义的,因此这仅用于非分组列在功能上依赖于分组列的查询...因为,在那种情况下,“哪”行​​并不重要,因为它们在每个组中都是相同的。

MySQL extends the standard SQL use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the [queries excluding non-aggregated columns are] legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. [emphasis added]

https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html

您可以通过包含 ONLY_FULL_GROUP_BY in @@SQL_MODE 来禁用此行为.

关于mysql - 为什么这个 MySQL 语句中的 GROUP BY 子句不抛出错误?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34540244/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com