《Oracle 入門教程》第 06 篇 排序顯示

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        6.1 基于單個字段排序
        6.2 基于多個字段排序
        6.3 基于表達式排序
        6.4 空值排序
        6.5 中文排序

默認情況下，Oracle 不會對查詢返回的結(jié)果進行排序，也就意味著查詢結(jié)果的順序是不確定的。如果想要按照某種規(guī)則對結(jié)果進行排序顯示，例如按照薪水從高到低，或者按照入職先后進行排序，可以使用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|
關(guān)羽     | 26000|
張飛     | 24000|



對于 ASC 排序，數(shù)字按照從小到大的順序排序，字符按照編碼的順序排列，日期時間按照從早到晚的順序排列；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|
關(guān)羽     |2000-01-01 00:00:00|


查詢結(jié)果中 3 位員工的入職日期完全相同。那么他們誰排在前面，誰排在后面呢？答案是不確定。如果想要解決這個問題，需要使用多列排序。
6.2 基于多個字段排序

多列排序是指基于多個字段的值排序，多個字段使用逗號進行分隔。多列排序的語法如下：

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



對于多列排序，先基于第一個字段進行排序；對于第一個字段排序相同的數(shù)據(jù)，再基于第二個字段進行排序；依此類推。

例如，以下語句查詢行政管理部（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  |
關(guān)羽     | 26000|guanyu@shuguo.com  |
張飛     | 24000|zhangfei@shuguo.com|



查詢沒有返回排序字段 hire_date，也就是說 ORDER BY 中的字段不一定需要出現(xiàn)在 SELECT 列表中。
6.3 基于表達式排序

除了使用字段的值進行排序之外，也可以基于表達式的結(jié)果進行排序。例如，以下語句返回了人力資源部（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) 函數(shù)用于將 bonus 為空的數(shù)據(jù)轉(zhuǎn)換為 0；該函數(shù)將會在后續(xù)文章中進行介紹。查詢按照 total_income 字段進行排序，它是一個表達式的值。

除了使用字段名或者表達式之外，也可以使用它們在 SELECT 列表中出現(xiàn)的編號指定排序。例如，上面的語句可以改寫如下：

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）在數(shù)據(jù)庫中表示未知或者缺失的值，例如沒有上級領(lǐng)導(dǎo)（manager）或者未知的電子郵箱。如果排序的字段中存在空值時，結(jié)果會怎么樣呢？以下語句按照獎金從低到高進行排序：

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

EMP_NAME|BONUS|
--------|-----|
諸葛亮   | 8000|
魏延     |     |
黃忠     |     |


從查詢結(jié)果可以看到，空值排在了最后。也就是說，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表示空值排在其他數(shù)據(jù)之前，NULLS LAST表示空值排在其他數(shù)據(jù)之后。對于 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|



另一種指定空值排序位置的方法是使用相關(guān)函數(shù)（例如 COALESCE）將空值轉(zhuǎn)換為一個指定的值。例如，以下語句將獎金為空的數(shù)據(jù)轉(zhuǎn)換為 0，實現(xiàn)了和上面的示例相同的排序：

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 中文排序

我們在創(chuàng)建 Oracle 數(shù)據(jù)庫時需要指定一個字符集（Charset）。字符集決定了能夠存儲哪些字符，比如 ASCII 字符集只能存儲簡單的英文、數(shù)字和一些控制字符；GB2312 字符集可以存儲中文；Unicode 字符集能夠支持世界上的各種語言。

Oracle 字符集同時還決定了支持的字符排序順序，包括是否區(qū)分大小寫，是否區(qū)分重音等。對于中文而言，排序方式與英文有所不同；中文通常需要按照拼音、偏旁部首或者筆畫進行排序。

Oracle 默認使用 AL32UTF8 字符集，不支持中文排序規(guī)則。不過我們可以通過一個轉(zhuǎn)換函數(shù)實現(xiàn)該功能，以下示例按照員工姓名的拼音進行排序：

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

EMP_NAME|
--------|
關(guān)平     |
關(guān)興     |
廖化     |
馬岱     |
張苞     |
趙氏     |
趙統(tǒng)     |
趙云     |
周倉     |



其中，NLSSORT 是一個函數(shù)，返回了按照某種排序規(guī)則得到的字符序列；SCHINESE_PINYIN_M 表示中文的拼音排序規(guī)則。除此之外，Oracle 還支持按偏旁部首進行排序：SCHINESE_RADICAL_M，以及按筆畫進行排序：SCHINESE_STROKE_M。

    ??除了以上排序方法之外，還可以通過 CASE 表達式實現(xiàn)自定義的排序規(guī)則，也就是手動指定不同數(shù)據(jù)的排序順序。我們將會在后續(xù)文章中會介紹 CASE 表達式的作用。