PostgreSQL ORDER BY 运算符用法与实例

在 PostgreSQL 中，ORDER BY 子句用来对 SELECT 语句返回的结果集排序。如果您没有为 SELECT 语句指定 ORDER BY 子句，SELECT 语句返回的结果集是按照数据库默认的规则排序的。

PostgreSQL `ORDER BY` 子句介绍

使用 ORDER BY 子句可以让我们对 SELECT 语句返回的结果集按照一个或这多个列升序或者降序排序。 ORDER BY 子句的语法如下：

SELECT
   column1, column2, ...
FROM
   table_name
[WHERE clause]
ORDER BY
   column1 [ASC|DESC],
   [column2 [ASC|DESC],
   ...]
   [NULLS FIRST | NULLS LAST]
   ;

说明：

您可以为 ORDER BY 子句指定一个或多个列或者表达式。
ASC 代表升序，DESC 代表降序。这是可选的，默认值是 ASC。
当指定多个排序表达式时，首先按照前面的表达式排序，其次按照后面的列排序。
NULLS FIRST 和 NULLS LAST 用来指定对 null 值排序规则：
- NULLS FIRST： null 值在非 null 值之前。
- NULLS LAST： null 值在非 null 值之后。
默认情况下，PostgreSQL 采用升序排序时采用 NULLS LAST，降序排序时采用 NULLS FIRST。也就是说， PostgreSQL 默认 null 值比非 null 值大。

PostgreSQL ORDER BY 排序规则说明

下面说明多种情况下的排序规则：

ORDER BY column ASC;

此 ORDER BY 子句对结果集按 column 列的值升序排序。
ORDER BY column DESC;

此 ORDER BY 子句对结果集按 column 列的值降序排序。
ORDER BY column;

此 ORDER BY 子句对结果集按 column 列的值升序排序。这个语句等效于： ORDER BY column ASC;。
ORDER BY column1, column2;

此 ORDER BY 子句对结果集先按 column1 列的值升序排序，然后再按 column2 列的值升序排序。

也就是说主排序按 column1 列升序排序，在主排序的基础上，那些 column1 列值相同的行，再按 column2 列升序排序。
ORDER BY column1 DESC, column2;

此 ORDER BY 子句对结果集先按 column1 列的值降序排序，然后再按按 column2 列的值升序排序。

也就是说主排序按 column1 列降序排序，在主排序的基础上，那些 column1 列值相同的行，再按 column2 列升序排序。

PostgreSQL ORDER BY 实例

我们将使用 Sakila 示例数据库中的表进行演示，请您先在 PostgreSQL 中安装 Sakila 示例数据库。

在以下实例中，actor 表中存储了演员的信息。

按字段升序排序

以下 SQL 语句使用 ORDER BY 子句按演员姓氏升序进行排序。

SELECT
    actor_id, first_name, last_name
FROM
    actor
ORDER BY last_name;

 actor_id | first_name  |  last_name
----------+-------------+--------------
      182 | DEBBIE      | AKROYD
       92 | KIRSTEN     | AKROYD
       58 | CHRISTIAN   | AKROYD
      194 | MERYL       | ALLEN
      145 | KIM         | ALLEN
      118 | CUBA        | ALLEN
       76 | ANGELINA    | ASTAIRE
      112 | RUSSELL     | BACALL
      190 | AUDREY      | BAILEY
       67 | JESSICA     | BAILEY
      115 | HARRISON    | BALE
      187 | RENEE       | BALL
...
(200 rows)

按字段降序排序

以下 SQL 语句使用 ORDER BY 子句按演员姓氏降序进行排序。

SELECT
    actor_id, first_name, last_name
FROM
    actor
ORDER BY last_name DESC;

 actor_id | first_name  |  last_name
----------+-------------+--------------
      186 | JULIA       | ZELLWEGER
      111 | CAMERON     | ZELLWEGER
       85 | MINNIE      | ZELLWEGER
       63 | CAMERON     | WRAY
       13 | UMA         | WOOD
      156 | FAY         | WOOD
      144 | ANGELA      | WITHERSPOON
       68 | RIP         | WINSLET
      147 | FAY         | WINSLET
      168 | WILL        | WILSON
      164 | HUMPHREY    | WILLIS
       96 | GENE        | WILLIS
...
(200 rows)

按多字段排序

以下 SQL 语句使用 ORDER BY 子句先按演员姓氏升序排序，再按演员名字升序排序。

SELECT
    actor_id, first_name, last_name
FROM
    actor
ORDER BY last_name, first_name;

 actor_id | first_name  |  last_name
----------+-------------+--------------
       58 | CHRISTIAN   | AKROYD
      182 | DEBBIE      | AKROYD
       92 | KIRSTEN     | AKROYD
      118 | CUBA        | ALLEN
      145 | KIM         | ALLEN
      194 | MERYL       | ALLEN
       76 | ANGELINA    | ASTAIRE
      112 | RUSSELL     | BACALL
      190 | AUDREY      | BAILEY
       67 | JESSICA     | BAILEY
      115 | HARRISON    | BALE
      187 | RENEE       | BALL
...
(200 rows)

按自定义顺序排序

有时候单纯的按照字段的值排序并不能满足要求，我们需要按照自定义的顺序的排序。比如，我们需要按照电影分级 'G', 'PG', 'PG-13', 'R', 'NC-17' 的顺序对影片进行排序。

对于这样的需求，它可以理解为按照列表中元素的索引位置进行排序。我们使用 CASE 子句函数实现它。

在以下实例中，我们使用 Sakila 示例数据库中的 film 表作为演示。

假设您要根据影片的分级按照的 'G', 'PG', 'PG-13', 'R', 'NC-17' 顺序对影片进行排序。

使用 `CASE` 实现自定义排序

SELECT
    film_id, title, rating
FROM
    film
ORDER BY CASE rating
    WHEN 'G' THEN 1
    WHEN 'PG' THEN 2
    WHEN 'PG-13' THEN 3
    WHEN 'R' THEN 4
    WHEN 'NC-17' THEN 5
END;


     357 | GILBERT PELICAN             | G
     597 | MOONWALKER FOOL             | G
     354 | GHOST GROUNDHOG             | G
...
     595 | MOON BUNCH                  | PG
       6 | AGENT TRUMAN                | PG
     600 | MOTIONS DETAILS             | PG
...
       9 | ALABAMA DEVIL               | PG-13
     657 | PARADISE SABRINA            | PG-13
     956 | WANDA CHAMBER               | PG-13
...
     749 | RULES HUMAN                 | R
       8 | AIRPORT POLLOCK             | R
      17 | ALONE TRIP                  | R
...
     520 | LICENSE WEEKEND             | NC-17
     517 | LESSON CLEOPATRA            | NC-17
     114 | CAMELOT VACATION            | NC-17
...
(1000 rows)

在这个例子中，我们使用 CASE 子句将电影的分级转换为一个数字。然后使用 ORDER BY 按照这个数字进行排序。

ORDER BY 和 NULL

在 PostgreSQL 中的升序排序中， NULL 值出现在非 NULL 值之前。

我们下面的实例使用以下临时数据作为演示：

SELECT 'A' AS v
UNION ALL
SELECT 'B' AS v
UNION ALL
SELECT NULL AS v
UNION ALL
SELECT '0' AS v
UNION ALL
SELECT '1' AS v;

   v
--------
 A
 B
 <null>
 0
 1
(5 rows)

当我们使用 ORDER BY 子句升序 ASC 排序时， NULL 值默认排在非 NULL 值的后面。如下：

SELECT 'A' AS v
UNION ALL
SELECT 'B' AS v
UNION ALL
SELECT NULL AS v
UNION ALL
SELECT '0' AS v
UNION ALL
SELECT '1' AS v
ORDER BY v;

   v
--------
 0
 1
 A
 B
 <null>

这里，ASC 排序采用 NULLS LAST 规则，所以 NULL 值在最后。如果您想要改用 NULLS FIRST，请执行以下语句：

SELECT 'A' AS v
UNION ALL
SELECT 'B' AS v
UNION ALL
SELECT NULL AS v
UNION ALL
SELECT '0' AS v
UNION ALL
SELECT '1' AS v
ORDER BY v NULLS FIRST;

   v
--------
 <null>
 0
 1
 A
 B

当我们使用 ORDER BY 子句降序 DESC 排序时， NULL 值排在非 NULL 值的前面。这是因为 DESC 排序默认采用 NULLS FIRST 规则。如下：

SELECT 'A' AS v
UNION ALL
SELECT 'B' AS v
UNION ALL
SELECT NULL AS v
UNION ALL
SELECT '0' AS v
UNION ALL
SELECT '1' AS v
ORDER BY v DESC;

   v
--------
 <null>
 B
 A
 1
 0

结论

PostgreSQL ORDER BY 子句用来对 SELECT 语句返回的结果集排序。如果您没有为 SELECT 语句指定 ORDER BY 子句，SELECT 语句返回的结果集是按照数据库默认的规则排序的。

ORDER BY 子句的用法要点如下：

使用 ORDER BY 子句按一列或多列对结果集进行排序。
使用 ASC 选项对结果集进行升序排序，使用 DESC 选项对结果集进行降序排序。
使用 NULLS FIRST 或者 NULLS LAST 改变对 NULL 值的处理规则。

PostgreSQL ORDER BY 子句介绍