排序

# 90.排序

通过SQL查询得到的结果，排列顺序通常是随机的，可以使用order by 子句进行排序。

‍

# ORDER BY 子句

语法：

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 ORDER BY <排序基准列1>, <排序基准列2>, ……

1
2
3

‍

例如，按照销售单价由低到高，也就是升序排列时：

SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY sale_price;

--结果：
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0008       | 圆珠笔       |        100 |           NULL |
| 0002       | 打孔器       |        500 |            320 |
| 0006       | 叉子         |        500 |           NULL |
| 0007       | 擦菜板       |        880 |            790 |
| 0001       | T恤衫        |       1000 |            500 |
| 0004       | 菜刀         |       3000 |           2800 |
| 0003       | 运动T恤      |       4000 |           2800 |
| 0005       | 高压锅       |       6800 |           5000 |
+------------+--------------+------------+----------------+
8 rows in set (4.48 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

‍

书写顺序：ORDER BY 子句需要写在 SELECT 语句的末尾。这是因为对数据行进行排序的操作必须在结果即将返回时执行。ORDER BY 子句中书写的列名称为排序键。

‍

▶ 目前学过的子句书写顺序：SELECT 子句 → FROM 子句 → WHERE 子句 → GROUP BY 子句 → HAVING 子句 → ORDER BY 子句

‍

# 升序与降序

默认情况下，排序是按升序排列的（数据从上到下，从小到大）；

使用 desc 关键字，可以实现降序排序：

SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY sale_price DESC;


--结果：
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0005       | 高压锅       |       6800 |           5000 |
| 0003       | 运动T恤      |       4000 |           2800 |
| 0004       | 菜刀         |       3000 |           2800 |
| 0001       | T恤衫        |       1000 |            500 |
| 0007       | 擦菜板       |        880 |            790 |
| 0002       | 打孔器       |        500 |            320 |
| 0006       | 叉子         |        500 |           NULL |
| 0008       | 圆珠笔       |        100 |           NULL |
+------------+--------------+------------+----------------+
8 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

如上所示，这次销售单价最高（6800 日元）的高压锅排在了第一位。

‍

其实，使用升序进行排列时，正式的书写方式应该是使用关键字 ASC ，但是省略该关键字时会默认使用升序进行排序。

ASC 和 DESC 是 ascendent（上升的）和 descendent（下降的）这两个单词的缩写。

‍

# 指定多个排序键

销售单价（sale_price）为 500 日元的商品有 2 件，那么如果值是一样的，会怎么排序呢？答案是随机的。

如果想要更细致的排序，则需要再添加一个排序键，例如排序商品编号：

SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY sale_price, product_id;

1
2
3
4

‍

执行结果：

00077

‍

这样一来，就可以在 ORDER BY 子句中同时指定多个排序键了。规则是优先使用左侧的键，如果该列存在相同值的话，再接着参考右侧的键。

由于 ASC 和 DESC 这两个关键字是以列为单位指定的，因此可以同时指定一个列为升序，指定其他列为降序。

‍

# null的排序

如果排序键中包含null，会怎么排序呢？

首先，排序的依据是两个值的大小；而之前我们说过，不能对null进行比较的，因此使用含有 NULL 的列作为排序键时， NULL 会在结果的开头或末尾汇总显示：

SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY purchase_price;

--结果：
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0006       | 叉子         |        500 |           NULL |
| 0008       | 圆珠笔       |        100 |           NULL |
| 0002       | 打孔器       |        500 |            320 |
| 0001       | T恤衫        |       1000 |            500 |
| 0007       | 擦菜板       |        880 |            790 |
| 0003       | 运动T恤      |       4000 |           2800 |
| 0004       | 菜刀         |       3000 |           2800 |
| 0005       | 高压锅       |       6800 |           5000 |
+------------+--------------+------------+----------------+
8 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

‍

究竟是在开头显示还是在末尾显示，并没有特殊规定。某些 DBMS 中可以指定 NULL 在开头或末尾显示，感兴趣的同学可以研究下。

‍

# 使用别名

在 ORDER BY 子句中却是允许使用别名的，这是因为子句的执行顺序：

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

这只是一个粗略的总结，虽然具体的执行顺序根据 DBMS 的不同而不同，有这样一个大致的印象就可以了。一定要记住 SELECT 子句的执行顺序在 GROUP BY 子句之后，ORDER BY 子句之前。因此，在执行 GROUP BY 子句时，SELECT 语句中定义的别名无法被识别（也是因为这一原因，HAVING 子句也不能使用别名）。对于在 SELECT 子句之后执行的 ORDER BY 子句来说，就没有这样的问题了。

‍

举例：

SELECT product_id AS id, product_name, sale_price AS sp, purchase_price
  FROM Product
ORDER BY sp, id;

--结果：
+------+--------------+------+----------------+
| id   | product_name | sp   | purchase_price |
+------+--------------+------+----------------+
| 0008 | 圆珠笔       |  100 |           NULL |
| 0002 | 打孔器       |  500 |            320 |
| 0006 | 叉子         |  500 |           NULL |
| 0007 | 擦菜板       |  880 |            790 |
| 0001 | T恤衫        | 1000 |            500 |
| 0004 | 菜刀         | 3000 |           2800 |
| 0003 | 运动T恤      | 4000 |           2800 |
| 0005 | 高压锅       | 6800 |           5000 |
+------+--------------+------+----------------+
8 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

‍

# ORDER BY 子句中可以使用的列

ORDER BY 子句中也可以使用存在于表中、但并不包含在 SELECT 子句之中的列：

SELECT product_name, sale_price, purchase_price
  FROM Product
ORDER BY product_id;

--结果：
+--------------+------------+----------------+
| product_name | sale_price | purchase_price |
+--------------+------------+----------------+
| T恤衫        |       1000 |            500 |
| 打孔器       |        500 |            320 |
| 运动T恤      |       4000 |           2800 |
| 菜刀         |       3000 |           2800 |
| 高压锅       |       6800 |           5000 |
| 叉子         |        500 |           NULL |
| 擦菜板       |        880 |            790 |
| 圆珠笔       |        100 |           NULL |
+--------------+------------+----------------+
8 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

‍

也可以使用聚合函数：

select product_type, count(*)
from Product
group by product_type
order by count(*);

--结果：
+--------------+----------+
| product_type | count(*) |
+--------------+----------+
| 衣服         |        2 |
| 办公用品     |        2 |
| 厨房用具     |        4 |
+--------------+----------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

‍

# 使用列编号（不推荐）

还可以使用在 SELECT 子句中出现的列所对应的编号。列编号是指 SELECT 子句中的列按照从左到右的顺序进行排列时所对应的编号（1, 2, 3,…）。以下两条SQL的等价的：

-- 通过列名指定
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY sale_price DESC, product_id;

-- 通过列编号指定
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY 3 DESC, 1;

1
2
3
4
5
6
7
8
9

虽然列编号使用起来非常方便，但我们并不推荐使用，原因有以下两点。

第一，代码阅读起来比较难。使用列编号时，如果只看 ORDER BY 子句是无法知道当前是按照哪一列进行排序的，只能去 SELECT 子句的列表中按照列编号进行确认。上述示例中 SELECT 子句的列数比较少，因此可能并没有什么明显的感觉。但是在实际应用中往往会出现列数很多的情况，而且 SELECT 子句和 ORDER BY 子句之间，还可能包含很复杂的 WHERE 子句和 HAVING 子句，直接人工确认实在太麻烦了。

第二，这也是最根本的问题，实际上，在 SQL-92（1992 年制定的 SQL 标准）中已经明确指出该排序功能将来会被删除。因此，虽然现在使用起来没有问题，但是将来随着 DBMS 的版本升级，可能原本能够正常执行的 SQL 突然就会出错。不光是这种单独使用的 SQL 语句，对于那些在系统中混合使用的 SQL 来说，更要极力避免。

上次更新: 2024/5/30 15:25:05

← 聚合查询数据的插入→