mysql - 如何在MySQL中正确的GROUP BY？

Question

我有以下（为了演示目的特意非规范化）样车表：

| CAR_ID | OWNER_ID | OWNER_NAME | COLOR |
|--------|----------|------------|-------|
|      1 |        1 |       John | White |
|      2 |        1 |       John | Black |
|      3 |        2 |       Mike | White |
|      4 |        2 |       Mike | Black |
|      5 |        2 |       Mike | Brown |
|      6 |        3 |       Tony | White |

如果我想计算每个车主的汽车数量并返回：

| OWNER_ID | OWNER_NAME | TOTAL |
|----------|------------|-------|
|        1 |       John |     2 |
|        2 |       Mike |     3 |
|        3 |       Tony |     1 |

我知道我可以写下以下问题：

SELECT owner_id, owner_name, COUNT(*) total FROM cars
GROUP BY owner_id, owner_name

但是，从 owner_name子句中删除 GROUP BY会得到相同的结果。
这两个查询有什么区别？
在什么情况下，我应该按 SELECT语句中的所有非agregated字段分组，在哪些情况下不应该分组？
你能举一个例子，在移除非聚合字段时，这个分组将返回不同的结果，并解释原因吗？

Answer 1

首先要说明的是SQL不是MySQL。
在标准SQL中，不允许按非聚合字段的子集分组。原因很简单。假设我正在运行此查询：

SELECT color, owner_name, COUNT(*) FROM cars
GROUP BY color

这个问题没有任何意义。即使试图解释也不可能。当然，它是选择颜色和计算每种颜色的汽车数量。但是，它也添加了 owner_name字段，并且对于给定的颜色可以有许多所有者，就像 White颜色的情况一样。所以如果一个 owner_name子句中只有一个字段可以有多个 color值。。。那么哪个 GROUP BY将被返回？
如果需要返回 owner_name，则应添加某种条件以仅选择其中一个条件，例如，按字母顺序排列的第一个条件，在本例中为 owner_name。该条件将导致添加聚合函数 John，然后查询将再次有意义，因为它将至少按select语句中的所有非agregated字段进行分组。
正如你所看到的，标准SQL在分组中是不灵活的，这是一个明确而实用的原因。如果不是这样，您可能会面临一种尴尬的情况，在这种情况下，列的值将是不可预测的，这不是一个好词，特别是如果正在运行的查询显示您的银行帐户事务。
既然如此，那么为什么MySQL会允许那些可能没有意义的查询呢？更糟糕的是，上面查询中的错误可能只是在语法上被检测到！简而言之，答案是：表现。长期的答案是，在某些情况下，基于数据关系，从组中获取不可预测的值将导致可预测的值。
如果您还没有找到答案，那么预测从组中获取不可预测元素的值的唯一方法是组中的所有元素都相同。这种情况的一个明显例子是在同一个问题的示例查询中。看看表中 MIN(owner_name)和 owner_id之间的关系。显然，给定任何 owner_name，例如 owner_id，您只能有一个不同的 2。即使有许多行，通过选择any，也会得到 owner_name结果。在正式的数据库术语中，这可以解释为 Mike功能决定 owner_id。
让我们更仔细地看一下这个完全工作的MySQL查询：

SELECT owner_id, owner_name, COUNT(*) total FROM cars
GROUP BY owner_id

如果有任何 owner_name则返回相同的 owner_id，因此将其添加到 owner_name子句不会导致返回更多行。即使添加聚合函数 GROUP BY也不会减少返回的行数。结果数据将是相同的。在这两种情况下，查询都将立即转换为合法的标准SQL查询，因为至少所有非聚合字段都将按分组。所以有三种方法可以得到相同的结果。
但是，正如我前面提到的，这种非标准分组具有性能优势。你可以查看这篇 so underrated link文章，其中有更详细的解释，但我将引用最重要的部分：
通过避免不必要的列排序和分组，可以使用此功能获得更好的性能。[…]服务器可以从每个组中自由选择任何值，因此，除非它们相同，否则选择的值是不确定的。
值得一提的是，结果不一定是错的，而是不确定的。换句话说，获得预期的结果并不意味着编写了正确的查询。编写正确的查询将始终为您提供预期的结果。
如您所见，将这个MySQL扩展应用到 MAX(owner_name)子句可能是值得的。无论如何，如果这还不是100%清楚，那么有一个经验法则可以确保您的分组总是正确的：总是至少按select子句中的所有非聚合字段分组。在某些情况下，您可能会浪费一些CPU周期，但这比返回不确定的结果要好。如果您仍然害怕不能正确分组，那么更改 GROUP BYSQL模式可能是最后的选择：）
希望你的分组是正确的和有效的。。。或者至少是正确的。