MySQL 聚合函数在 ORDER BY 子句中可以做什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13099282/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What can an aggregate function do in the ORDER BY clause?
提问by nawfal
Lets say I have a plant table:
假设我有一个植物表：
id fruit
1 banana
2 apple
3 orange
I can do these
我可以做这些
SELECT * FROM plant ORDER BY id;
SELECT * FROM plant ORDER BY fruit DESC;
which does the obvious thing.
这很明显。
But I was bitten by this, what does this do?
但是我被这个咬了，这有什么用？
SELECT * FROM plant ORDER BY SUM(id);
SELECT * FROM plant ORDER BY COUNT(fruit);
SELECT * FROM plant ORDER BY COUNT(*);
SELECT * FROM plant ORDER BY SUM(1) DESC;
All these return just the first row (which is with id = 1).
所有这些都只返回第一行（id = 1）。
- What's happening underhood?
- What are the scenarios where aggregate function will come in handy in
ORDER BY?
- 底下发生了什么？
- 聚合函数在哪些场景中会派上用场
ORDER BY？
回答by Emil Vikstr?m
Your results are more clear if you actually select the aggregate values instead of columns from the table:
如果您实际选择聚合值而不是表中的列，您的结果会更加清晰：
SELECT SUM(id) FROM plant ORDER BY SUM(id)
This will return the sum of all id's. This is of course a useless example because the aggregation will always create only one row, hence no need for ordering. The reason you get a row qith columns in your query is because MySQL picks one row, not at random but not deterministic either. It just so happens that it is the first column in the table in your case, but others may get another row depending on storage engine, primary keys and so on. Aggregation only in the ORDER BY clause is thus not very useful.
这将返回所有 id 的总和。这当然是一个无用的例子，因为聚合总是只创建一行，因此不需要排序。您在查询中获得一行 qith 列的原因是因为 MySQL 选择了一行，不是随机的，但也不是确定性的。碰巧它是您案例中表中的第一列，但其他人可能会根据存储引擎、主键等获得另一行。因此，仅在 ORDER BY 子句中的聚合不是很有用。
What you usually want to do is grouping by a certain field and then order the result set in some way:
您通常想要做的是按某个字段分组，然后以某种方式对结果集进行排序：
SELECT fruit, COUNT(*)
FROM plant
GROUP BY fruit
ORDER BY COUNT(*)
Now that's a more interesting query! This will give you one row for each fruit together with the total count for that fruit. Try adding some more apples and the ordering will actually start making sense:
现在这是一个更有趣的查询！这将为您提供每个水果的一行以及该水果的总数。尝试添加更多苹果，然后排序实际上会开始有意义：
Complete table:
完整表：
+----+--------+
| id | fruit |
+----+--------+
| 1 | banana |
| 2 | apple |
| 3 | orange |
| 4 | apple |
| 5 | apple |
| 6 | banana |
+----+--------+
The query above:
上面的查询：
+--------+----------+
| fruit | COUNT(*) |
+--------+----------+
| orange | 1 |
| banana | 2 |
| apple | 3 |
+--------+----------+
回答by Mike Sherrill 'Cat Recall'
All these queries will all give you a syntax error on any SQL platform that complies with SQL standards.
所有这些查询都会在任何符合 SQL 标准的 SQL 平台上给你一个语法错误。
SELECT * FROM plant ORDER BY SUM(id);
SELECT * FROM plant ORDER BY COUNT(fruit);
SELECT * FROM plant ORDER BY COUNT(*);
SELECT * FROM plant ORDER BY SUM(1) DESC;
On PostgreSQL, for example, all those queries will raise the same error.
例如，在 PostgreSQL 上，所有这些查询都会引发相同的错误。
ERROR: column "plant.id" must appear in the GROUP BY clause or be used in an aggregate function
错误：列“plant.id”必须出现在 GROUP BY 子句中或用于聚合函数中
That means you're using a domain aggregate function without using GROUP BY. SQL Server and Oracle return similar error messages.
这意味着您正在使用域聚合函数而不使用 GROUP BY。SQL Server 和 Oracle 返回类似的错误消息。
MySQL's GROUP BY is known to be broken in several respects, at least as far as standard behavior is concerned. But the queries you posted were a new broken behavior to me, so +1 for that.
众所周知，MySQL 的 GROUP BY 在几个方面被破坏，至少就标准行为而言。但是您发布的查询对我来说是一种新的破坏行为，因此 +1。
Instead of trying to understand what it's doing under the hood, you're probably better off learning to write standard GROUP BY queries. MySQL willprocess standard GROUP BY statements correctly, as far as I know.
与其试图了解它在幕后做了什么，不如学习编写标准的 GROUP BY 查询。据我所知，MySQL将正确处理标准的 GROUP BY 语句。
Earlier versions of MySQL docs warned you about GROUP BY and hidden columns. (I don't have a reference, but this text is cited all over the place.)
早期版本的 MySQL 文档警告您有关 GROUP BY 和隐藏列的信息。（我没有参考文献，但是到处都引用了这段文字。）
Do not use this feature if the columns you omit from the GROUP BY part are not constant in the group. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.
如果您从 GROUP BY 部分省略的列在组中不是恒定的，请不要使用此功能。服务器可以自由地从组中返回任何值，因此除非所有值都相同，否则结果是不确定的。
More recent versions are a little different.
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.
您可以使用此功能通过避免不必要的列排序和分组来获得更好的性能。但是，这主要在未在 GROUP BY 中命名的每个非聚合列中的所有值对于每个组都相同时很有用。服务器可以自由地从每个组中选择任何值，因此除非它们相同，否则选择的值是不确定的。
Personally, I don't consider indeterminatea feature in SQL.
就个人而言，我不认为SQL 中的特性不确定。
回答by Guffa
When you use an aggregate like that, the query gets an implicit group by where the entire result is a single group.
Using an aggregate in order by is only useful if you also have a group by, so that you can have more than one row in the result.
当您使用这样的聚合时，查询将获得一个隐式组，其中整个结果是一个组。
仅当您还有一个 group by 时，按 order by 使用聚合才有用，这样您就可以在结果中拥有不止一行。
