Postgresql 中文操作指南

7.5. Sorting Rows (ORDER BY) #

在查询生成输出表（在处理 select 列表后）后，可以选择对其进行排序。如果未选择排序，则将以未指定顺序返回行。在这种情况下，实际顺序将取决于扫描和联接计划类型以及磁盘上的顺序，但不能依赖它。只有在明确选择排序步骤时，才能保证特定输出顺序。

After a query has produced an output table (after the select list has been processed) it can optionally be sorted. If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

ORDER BY 子句指定排序顺序：

The ORDER BY clause specifies the sort order:

SELECT select_list
    FROM table_expression
    ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
             [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

排序表达式可以是在查询的 select 列表中有效的任何表达式。示例包括：

The sort expression(s) can be any expression that would be valid in the query’s select list. An example is:

SELECT a, b FROM table1 ORDER BY a + b, c;

当指定了多个表达式时，使用后面的值对根据前面的值相等的行的顺序进行排序。每个表达式后面都可以跟一个可选的 ASC 或 DESC 关键字来将排序方向设置为升序或降序。 ASC 顺序是默认值。升序排列会优先显示较小的值，“较小的”根据 < 运算符来定义。类似地，降序是由 > 运算符决定的。 [6 ]

When more than one expression is specified, the later values are used to sort rows that are equal according to the earlier values. Each expression can be followed by an optional ASC or DESC keyword to set the sort direction to ascending or descending. ASC order is the default. Ascending order puts smaller values first, where “smaller” is defined in terms of the < operator. Similarly, descending order is determined with the > operator. [6]

NULLS FIRST 和 NULLS LAST 选项可用于确定 null 值在排序顺序中是排在非 null 值之前还是之后。默认情况下，null 值的排序方式就像它大于任何非 null 值；即，NULLS FIRST 是 DESC 顺序的默认值，否则采用 NULLS LAST。

The NULLS FIRST and NULLS LAST options can be used to determine whether nulls appear before or after non-null values in the sort ordering. By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise.

请注意，针对每一排序列单独考虑排序选项。例如，ORDER BY x, y DESC 表示 ORDER BY x ASC, y DESC，它与 ORDER BY x DESC, y DESC 不同。

Note that the ordering options are considered independently for each sort column. For example ORDER BY x, y DESC means ORDER BY x ASC, y DESC, which is not the same as ORDER BY x DESC, y DESC.

sort_expression 也可以是输出列的列标签或编号，例如：

A sort_expression can also be the column label or number of an output column, as in:

SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;

两者均按第一输出列进行排序。请注意，输出列名必须独立存在，即，它不能用于表达式中——例如，这是 not 正确的：

both of which sort by the first output column. Note that an output column name has to stand alone, that is, it cannot be used in an expression — for example, this is not correct:

SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;          -- wrong

此限制用于减少歧义。如果 ORDER BY 项是一个简单名称，它既可以匹配输出列名，又可以匹配表表达式的列，则仍然存在歧义。在这种情况中，使用输出列。只有当你使用 AS 将输出列重命名为与其他表列的名称匹配时，才会导致混淆。

This restriction is made to reduce ambiguity. There is still ambiguity if an ORDER BY item is a simple name that could match either an output column name or a column from the table expression. The output column is used in such cases. This would only cause confusion if you use AS to rename an output column to match some other table column’s name.

ORDER BY 可以应用于 UNION、INTERSECT 或 EXCEPT 组合的结果，但在这种情况下，只允许按输出列名称或编号进行排序，而不允许按表达式进行排序。

ORDER BY can be applied to the result of a UNION, INTERSECT, or EXCEPT combination, but in this case it is only permitted to sort by output column names or numbers, not by expressions.

[6 ] 实际上，PostgreSQL 使用 default B-tree operator class 作为表达式的类型来确定 ASC 和 DESC 的排序顺序。惯例上，数据类型将被设置为使 < 和 > 运算符与该排序顺序相对应，但用户定义的数据类型的设计人员可以选择执行不同的操作。

[6] Actually, PostgreSQL uses the default B-tree operator class for the expression’s data type to determine the sort ordering for ASC and DESC. Conventionally, data types will be set up so that the < and > operators correspond to this sort ordering, but a user-defined data type’s designer could choose to do something different.