7.5. Sorting Rows

7.5. Sorting Rows 7.5.行排序 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. 查询执行完后，可以选择为输出表排序（选择列表执行完后）。如果不指定排序，那么返回行无序。其实际返回顺序依赖于检索及连接的执行计划类型和行在磁盘上的顺序，但这是一种不可靠的排序。如果显式指定排序，那么输出中可以保证输出的顺序是唯一的。   The ORDER BY clause specifies the sort order: ORDER BY子句指定排序顺序：   SELECT select_listFROM table_expressionORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }][, sort_expression2 [ASC | DESC] [NULLS { FIRST |LAST }] …]   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;   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.  如果指定了多个表达式，则根据较早的值，再使用较晚的值对相等的行进行排序。每个表达式后可以跟一个可选的ASC或DESC关键字，以将排序方向设置为升序或降序。 默认为ASC顺序。升序将较小的值放在首位，其中“较小”是根据<运算符定义的。 同样，降序由>运算符确定。   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. NULLS FIRST和NULLS LAST选项定义了当排序是空值是在非空值之前还是之后。默认，空值在排序时比非空值大，也就是说，在DESC排序中，NULL FIRST为默认行为，而NULL LAST为ASC中的默认行为。   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 . 请注意，对于每个排序列，都将单独考虑排序选项。 例如，ORDER BY x，y DESC表示ORDER BY x ASC，y DESC，与ORDER BY x DESC，y DESC不同。   A sort_expression can also be the column label or number of an output column, as in: sort_expression也可以是列标签或者输出列的序号，例如：   SELECT a + b AS sum, c FROM table1 ORDER BY sum;SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;   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   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，依然存在歧义，比如引用的名称可能是输出列名也可能是表表达式中的列名。这种情况下，会使用输出列名。这只在当使用AS重命名输出列但却不小心与其他表的列名重名的时候，才会产生混淆。   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. ORDER BY可以应用到UNION,INTERSECT或者EXCEPT的结果中，但是这样的话，只可根据输出列名或序号进行排序，不可以使用表达式。 文章知识点与官方知识档案匹配，可进一步学习相关知识PostgreSQL技能树首页概览4552 人正在系统学习中

来源：丹心明月