《Oracle 入门教程》第 06 篇 排序显示

作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目录

        6.1 基于单个字段排序
        6.2 基于多个字段排序
        6.3 基于表达式排序
        6.4 空值排序
        6.5 中文排序

默认情况下，Oracle 不会对查询返回的结果进行排序，也就意味着查询结果的顺序是不确定的。如果想要按照某种规则对结果进行排序显示，例如按照薪水从高到低，或者按照入职先后进行排序，可以使用ORDER BY子句。
6.1 基于单个字段排序

按照单个字段的值进行排序称为单列排序。单列排序的语法如下：

SELECT column1, column2, ...
FROM table_name
[WHERE conditions]
ORDER BY column1 [ASC | DESC];


其中，ORDER BY用于指定排序的字段；ASC表示按照升序排序（Ascending），DESC表示按照降序排序（Descending），默认值为升序排序；如果指定了查询条件，ORDER BY 位于 WHERE 子句之后。

例如，以下语句返回了行政管理部（dept_id = 1）的所有员工，并且按照员工的薪水从高到低进行排序显示：

SELECT emp_name, salary
FROM employee
WHERE dept_id = 1
ORDER BY salary DESC;

EMP_NAME|SALARY|
--------|------|
刘备     | 30000|
关羽     | 26000|
张飞     | 24000|



对于 ASC 排序，数字按照从小到大的顺序排序，字符按照编码的顺序排列，日期时间按照从早到晚的顺序排列；DESC 排序正好相反。

我们再看一个按照日期排序的示例：

SELECT emp_name, hire_date
FROM employee
WHERE dept_id = 1
ORDER BY hire_date;

EMP_NAME|HIRE_DATE          |
--------|-------------------|
刘备     |2000-01-01 00:00:00|
张飞     |2000-01-01 00:00:00|
关羽     |2000-01-01 00:00:00|


查询结果中 3 位员工的入职日期完全相同。那么他们谁排在前面，谁排在后面呢？答案是不确定。如果想要解决这个问题，需要使用多列排序。
6.2 基于多个字段排序

多列排序是指基于多个字段的值排序，多个字段使用逗号进行分隔。多列排序的语法如下：

SELECT column1, column2, ...
FROM table_name
[WHERE conditions]
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;



对于多列排序，先基于第一个字段进行排序；对于第一个字段排序相同的数据，再基于第二个字段进行排序；依此类推。

例如，以下语句查询行政管理部（dept_id = 1）的员工信息；按照入职先后进行排序，入职日期相同再按照月薪从高到低排序：

SELECT emp_name, salary, email
FROM employee
WHERE dept_id = 1
ORDER BY hire_date, salary DESC;

EMP_NAME|SALARY|EMAIL              |
--------|------|-------------------|
刘备     | 30000|liubei@shuguo.com  |
关羽     | 26000|guanyu@shuguo.com  |
张飞     | 24000|zhangfei@shuguo.com|



查询没有返回排序字段 hire_date，也就是说 ORDER BY 中的字段不一定需要出现在 SELECT 列表中。
6.3 基于表达式排序

除了使用字段的值进行排序之外，也可以基于表达式的结果进行排序。例如，以下语句返回了人力资源部（dept_id = 2）的员工，并且按照年度总收入（年薪加奖金）从低到高进行排序显示：

SELECT emp_name, salary, bonus, salary * 12 + COALESCE(bonus, 0) AS total_income
FROM employee
WHERE dept_id = 2
ORDER BY total_income;

EMP_NAME|SALARY|BONUS|TOTAL_INCOME|
--------|------|-----|------------|
魏延     |  7500|     |       90000|
黄忠     |  8000|     |       96000|
诸葛亮   | 24000| 8000|      296000|


其中，COALESCE(bonus, 0) 函数用于将 bonus 为空的数据转换为 0；该函数将会在后续文章中进行介绍。查询按照 total_income 字段进行排序，它是一个表达式的值。

除了使用字段名或者表达式之外，也可以使用它们在 SELECT 列表中出现的编号指定排序。例如，上面的语句可以改写如下：

SELECT emp_name, salary, bonus, salary * 12 + COALESCE(bonus, 0) AS total_income
FROM employee
WHERE dept_id = 2
ORDER BY 4;


在查询列表中，total_income 是第 4 个字段；因此该语句也是按照年度总收入从低到高进行排序。
6.4 空值排序

空值（NULL）在数据库中表示未知或者缺失的值，例如没有上级领导（manager）或者未知的电子邮箱。如果排序的字段中存在空值时，结果会怎么样呢？以下语句按照奖金从低到高进行排序：

SELECT emp_name, bonus
FROM employee
WHERE dept_id = 2
ORDER BY bonus;

EMP_NAME|BONUS|
--------|-----|
诸葛亮   | 8000|
魏延     |     |
黄忠     |     |


从查询结果可以看到，空值排在了最后。也就是说，Oracle 认为空值最大，升序时排在最后面，降序时排在最前面。

实际上，Oracle 提供了指定空值排序位置的子句，指定的语法如下：

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


其中，NULLS FIRST表示空值排在其他数据之前，NULLS LAST表示空值排在其他数据之后。对于 ASC 排序，默认使用 NULLS LAST；对于 DESC 排序，默认使用 NULLS FIRST。

因此，我们可以使用 NULLS FIRST 将上面示例中的空值排在最前面：

SELECT emp_name, bonus
FROM employee
WHERE dept_id = 2
ORDER BY bonus NULLS FIRST;

EMP_NAME|BONUS|
--------|-----|
魏延     |     |
黄忠     |     |
诸葛亮   | 8000|



另一种指定空值排序位置的方法是使用相关函数（例如 COALESCE）将空值转换为一个指定的值。例如，以下语句将奖金为空的数据转换为 0，实现了和上面的示例相同的排序：

SELECT emp_name, COALESCE(bonus, 0) AS bonus
FROM employee
WHERE dept_id = 2
ORDER BY 2;

EMP_NAME|BONUS|
--------|-----|
黄忠     |    0|
魏延     |    0|
诸葛亮   | 8000|


6.5 中文排序

我们在创建 Oracle 数据库时需要指定一个字符集（Charset）。字符集决定了能够存储哪些字符，比如 ASCII 字符集只能存储简单的英文、数字和一些控制字符；GB2312 字符集可以存储中文；Unicode 字符集能够支持世界上的各种语言。

Oracle 字符集同时还决定了支持的字符排序顺序，包括是否区分大小写，是否区分重音等。对于中文而言，排序方式与英文有所不同；中文通常需要按照拼音、偏旁部首或者笔画进行排序。

Oracle 默认使用 AL32UTF8 字符集，不支持中文排序规则。不过我们可以通过一个转换函数实现该功能，以下示例按照员工姓名的拼音进行排序：

SELECT emp_name
  FROM employee
 WHERE dept_id = 4
 ORDER BY NLSSORT(emp_name,'NLS_SORT = SCHINESE_PINYIN_M');

EMP_NAME|
--------|
关平     |
关兴     |
廖化     |
马岱     |
张苞     |
赵氏     |
赵统     |
赵云     |
周仓     |



其中，NLSSORT 是一个函数，返回了按照某种排序规则得到的字符序列；SCHINESE_PINYIN_M 表示中文的拼音排序规则。除此之外，Oracle 还支持按偏旁部首进行排序：SCHINESE_RADICAL_M，以及按笔画进行排序：SCHINESE_STROKE_M。

    📝除了以上排序方法之外，还可以通过 CASE 表达式实现自定义的排序规则，也就是手动指定不同数据的排序顺序。我们将会在后续文章中会介绍 CASE 表达式的作用。