postgresql order by 子句——迹忆客-ag捕鱼王app官网

postgresql order by子句用于根据一列或多列按升序或降序对select 查询的数据进行排序。

语法

order by 子句的基础语法如下：

select column-list
from table_name
[where condition]
[order by column1, column2, .. columnn] [asc | desc];

您可以在 order by 中使用一列或者多列，但是必须保证要排序的列必须存在。

asc 表示升序，desc 表示降序。

示例

创建 company 表（ ），数据内容如下：

jiyik_db=# 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)

下面示例将对结果根据 age 字段值进行升序排列：

jiyik_db=# select * from company order by age asc;

结果如下：

 id | name  | age |                      address                       | salary 
---- ------- ----- ---------------------------------------------------- --------
  6 | kim   |  22 | south-hall                                         |  45000
  3 | teddy |  23 | norway                                             |  20000
  7 | james |  24 | houston                                            |  10000
  4 | mark  |  25 | rich-mond                                          |  65000
  2 | allen |  25 | texas                                              |  15000
  5 | david |  27 | texas                                              |  85000
  1 | paul  |  32 | california                                         |  20000
(7 rows)

下面示例将对结果根据 name 字段值和 salary 字段值进行升序排序：

jiyik_db=# select * from company order by name, salary asc;

结果如下：

 id | name  | age |                      address                       | salary 
---- ------- ----- ---------------------------------------------------- --------
  2 | allen |  25 | texas                                              |  15000
  5 | david |  27 | texas                                              |  85000
  7 | james |  24 | houston                                            |  10000
  6 | kim   |  22 | south-hall                                         |  45000
  4 | mark  |  25 | rich-mond                                          |  65000
  1 | paul  |  32 | california                                         |  20000
  3 | teddy |  23 | norway                                             |  20000
(7 rows)

下面示例将对结果根据name字段值进行降序排列：

jiyik_db=# select * from company order by name desc;

结果如下：

 id | name  | age |                      address                       | salary 
---- ------- ----- ---------------------------------------------------- --------
  3 | teddy |  23 | norway                                             |  20000
  1 | paul  |  32 | california                                         |  20000
  4 | mark  |  25 | rich-mond                                          |  65000
  6 | kim   |  22 | south-hall                                         |  45000
  7 | james |  24 | houston                                            |  10000
  5 | david |  27 | texas                                              |  85000
  2 | allen |  25 | texas                                              |  15000
(7 rows)