DevShelf

Appearance

PostgreSQL ORDER BY 运算符用法与实例 ​

本文介绍了在 PostgreSQL 中如何使用 ORDER BY 子句排序 SELECT 语句返回的结果集。

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

PostgreSQL ORDER BY 子句介绍 ​

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

sql
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 子句按演员姓氏升序进行排序。

sql
SELECT
    actor_id, first_name, last_name
FROM
    actor
ORDER BY last_name;
bash
 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 子句按演员姓氏降序进行排序。

sql
SELECT
    actor_id, first_name, last_name
FROM
    actor
ORDER BY last_name DESC;
sql
 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 子句先按演员姓氏升序排序，再按演员名字升序排序。

sql
SELECT
    actor_id, first_name, last_name
FROM
    actor
ORDER BY last_name, first_name;
sql
 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 实现自定义排序 ​

sql
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;
bash
     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 值之前。

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

sql
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;
bash
   v
--------
 A
 B
 <null>
 0
 1
(5 rows)

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

sql
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;
bash
   v
--------
 0
 1
 A
 B
 <null>

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

sql
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;
bash
   v
--------
 <null>
 0
 1
 A
 B

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

sql
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;
bash
   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 值的处理规则。