聚合函数在ORDER BY子句中可以做什么? [英] What can an aggregate function do in the ORDER BY clause?

问题描述

让我说我有一张植物桌:

Lets say I have a plant table:

id fruit
1  banana
2  apple
3  orange

我可以做到这些

SELECT * FROM plant ORDER BY id;
SELECT * FROM plant ORDER BY fruit DESC;

这很明显.

但是我被这个咬了，这是做什么的?

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;

所有这些仅返回第一行(id = 1).

All these return just the first row (which is with id = 1).

1. 内幕发生了什么?
2. 在什么情况下聚合函数将在ORDER BY中派上用场?

1. What's happening underhood?
2. What are the scenarios where aggregate function will come in handy in ORDER BY?

推荐答案

如果您实际上选择的是汇总值而不是表中的列，则结果将更加清晰:

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)

这将返回所有ID的总和.这当然是一个无用的示例，因为聚合将始终仅创建一行，因此无需排序.在查询中获得第qith列的原因是因为MySQL选择了一行，不是随机而是确定性的.碰巧的是它是您所用表的第一列，但是其他人可能会根据存储引擎，主键等获得另一行.因此，仅在ORDER BY子句中进行聚合不是很有用.

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.

通常要做的是按特定字段分组，然后以某种方式对结果集进行排序:

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:

完整表格:

+----+--------+
| id | fruit  |
+----+--------+
|  1 | banana |
|  2 | apple  |
|  3 | orange |
|  4 | apple  |
|  5 | apple  |
|  6 | banana |
+----+--------+

上面的查询:

+--------+----------+
| fruit  | COUNT(*) |
+--------+----------+
| orange |        1 |
| banana |        2 |
| apple  |        3 |
+--------+----------+

这篇关于聚合函数在ORDER BY子句中可以做什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！