Postgresql 简明教程

PostgreSQL - ORDER BY Clause

PostgreSQL ORDER BY 子句用于按一个或多个列对数据进行升序或降序排序。

The PostgreSQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns.

Syntax

ORDER BY 子句的基本语法如下 −

The basic syntax of ORDER BY clause is as follows −

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

您可以在 ORDER BY 子句中使用多个列。务必确保用于排序的列必须在列列表中可用。

You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort, that column should be available in column-list.

Example

考虑 COMPANY 表具有以下记录：

Consider the table COMPANY having records as follows −

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

以下是一个示例，按 SALARY 升序排列结果 −

The following is an example, which would sort the result in ascending order by SALARY −

testdb=# SELECT * FROM COMPANY ORDER BY AGE ASC;

这将产生以下结果 -

This would produce the following result −

  id | name  | age | address    | salary
 ----+-------+-----+------------+--------
   6 | Kim   |  22 | South-Hall |  45000
   3 | Teddy |  23 | Norway     |  20000
   7 | James |  24 | Houston    |  10000
   8 | Paul  |  24 | Houston    |  20000
   4 | Mark  |  25 | Rich-Mond  |  65000
   2 | Allen |  25 | Texas      |  15000
   5 | David |  27 | Texas      |  85000
   1 | Paul  |  32 | California |  20000
   9 | James |  44 | Norway     |   5000
  10 | James |  45 | Texas      |   5000
(10 rows)

以下是一个示例，按 NAME 和 SALARY 升序排列结果 −

The following is an example, which would sort the result in ascending order by NAME and SALARY −

testdb=# SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

这将产生以下结果 -

This would produce the following result −

 id | name  | age | address      | salary
----+-------+-----+--------------+--------
  2 | Allen |  25 | Texas        |  15000
  5 | David |  27 | Texas        |  85000
 10 | James |  45 | Texas        |   5000
  9 | James |  44 | Norway       |   5000
  7 | James |  24 | Houston      |  10000
  6 | Kim   |  22 | South-Hall   |  45000
  4 | Mark  |  25 | Rich-Mond    |  65000
  1 | Paul  |  32 | California   |  20000
  8 | Paul  |  24 | Houston      |  20000
  3 | Teddy |  23 | Norway       |  20000
(10 rows)

以下是一个示例，按 NAME 降序排列结果 −

The following is an example, which would sort the result in descending order by NAME −

testdb=# SELECT * FROM COMPANY ORDER BY NAME DESC;

这将产生以下结果 -

This would produce the following result −

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  3 | Teddy |  23 | Norway     |  20000
  1 | Paul  |  32 | California |  20000
  8 | Paul  |  24 | Houston    |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  9 | James |  44 | Norway     |   5000
 10 | James |  45 | Texas      |   5000
  5 | David |  27 | Texas      |  85000
  2 | Allen |  25 | Texas      |  15000
(10 rows)