ORDER BY的用法都有哪些

今天就跟大家聊聊有關ORDER BY的用法都有哪些，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。

小伙伴們在進行SQL排序時，都能很自然的使用到ORDER BY。不管是默認ASC的升序，還是DESC降序，幾乎都是信手拈來。

今天給大家分享一些你可能不知道的ORDER BY用法。

一、ORDER BY返回的是游標而不是集合

SQL的理論其實是集合論，常見的類似求數據的交集、并集、差集都可以使用集合的思維來求解。

集合中的行之間沒有預先定義的順序，它只是成員的一種邏輯組合，成員之間的順序無關緊要。

如下圖，每一個括號里的內容就是一條記錄，在沒排序前，他們都是隨機分布在集合中。

Student(ID,Name,Age)

Student集合

但是對于帶有排序作用的ORDER BY子句的查詢，它返回的是一個對象，其中的行按特定的順序組織在一起，我們把這種對象稱為游標。

如下圖，經過對Student表的ID進行ORDER BY排序后，Student表變成了有序對象，也就是我們上面說的游標。

Student(ID,Name,Age)

Student對象

二、ORDER BY子句是唯一能重用列別名的一步

注：markdown對代碼塊的語法是開始和結束行都要添加：```,其中 ` 為windows鍵盤左上角那

這里涉及SQL語句的語法順序和執行順序了，我們常見的SQL語法順序如下： 

SELECT DISTINCT  <Top Num> <select list>FROM [left_table]<join_type> JOIN <right_table>ON <join_condition>WHERE <where_condition>GROUP BY <group_by_list>WITH <CUBE | RollUP>HAVING <having_condition>ORDER BY <order_by_list>

而數據庫引擎在執行SQL語句并不是從SELECT開始執行，而是從FROM開始，具體執行順序如下(關鍵字前面的數字代表SQL執行的順序步驟)： 

(8)SELECT (9)DISTINCT  (11)<Top Num> <select list>      (1)FROM [left_table]      (3)<join_type> JOIN <right_table>      (2)        ON <join_condition>      (4)WHERE <where_condition>      (5)GROUP BY <group_by_list>      (6)WITH <CUBE | RollUP>      (7)HAVING <having_condition>      (10)ORDER BY <order_by_list>

從上面可以看到SELECT在HAVING后才開始執行，這個時候SELECT后面列的別名只對后續的步驟生效，而對SELECT前面的步驟是無效的。所以如果你在WHERE，GROUP BY，或HAVING后面使用列的別名均會報錯。

我們舉例測試一下。

示例表Customers結構及數據如下：

1、WHERE后面不使用別名的情況

SELECT   姓名 AS Name,  地址 AS Address,  城市 AS City  FROM Customers  WHERE 城市='廣州'

結果如下：

2、WHERE后面使用列別名的情況

SELECT   姓名 AS Name,  地址 AS Address,  城市 AS City  FROM Customers  WHERE City='廣州'

執行結果如下：

從返回的消息中我們可以看到，重命名后的City并不能被WHERE識別，所以才會報“列名'City'無效”的提示。

其他關鍵字大家也可以使用上述方法進行測試，下面我們測試GROUP BY和HAVING后面使用列別名的情況。

3、測試GROUP BY后使用列別名

SELECT   城市 AS City  FROM Customers  GROUP BY City

結果如下：

4、測試HAVING后使用列別名

SELECT   城市 AS City  FROM Customers  GROUP BY 城市  HAVING COUNT(City)>1

結果如下：

5、測試ORDER BY后面使用列別名

SELECT   姓名 AS Name,  地址 AS Address,  城市 AS City  FROM Customers  ORDER BY City

結果如下：

從上面的幾個測試示例的結果中，可以得出我們的結論是正確的：ORDER BY子句是唯一能重用列別名的一步。

三、謹慎使用ORDER BY 后面接數字的方式來進行排序

有些小伙伴為了圖省事，喜歡在ORDER BY后面寫數字，具體示例如下：

SELECT   姓名 AS Name,  地址 AS Address,  城市 AS City  FROM Customers  ORDER BY 1,2,3

結果如下：

這樣寫的結果，針對當前的查詢是正確沒有問題的，ORDER BY后面的數字1,2,3分別代表SELECT后面的第1，第2，第3個字段(也就是Name，Address，City)。

可是當查詢的列發生改變，忘了修改ORDER BY列表。特別是當查詢語句很長時，要找到ORDER BY與SELECT列表中的哪個列相對應會非常困難。

例如：

SELECT   客戶ID AS ID,  姓名 AS Name,  地址 AS Address,  城市 AS City  FROM Customers  ORDER BY 1,2,3

由于增加了一列“客戶ID”，原本的題意還是對Name，Address，City排序，但是因為使用了ORDER BY加數字，排序后的結果如下：

得到的結果并不是我們想要的，所以請慎用ORDER BY加數字，盡量使用ORDER BY加列名或列別名。

四、表達式不能使用ORDER BY排序

表表達式包括視圖，內聯表值函數，派生表(子查詢)和公用表表達式(CTE)。

例如下面的視圖是無效的

CREATE VIEW V_Customers AS  SELECT   客戶ID AS ID,  姓名 AS Name,  地址 AS Address,  城市 AS City  FROM Customers  ORDER BY ID,Name,Address

結果如下：

這個錯誤是不是很熟悉？因為很多小伙伴經常喜歡在視圖或子查詢里面加ORDER BY，然后一執行就會報這個錯。

根本原因不敢妄加斷定，因為搜尋了很多文獻資料也沒給出一個具體的說法。

這里我猜測是因為視圖，內聯表值函數，派生表(子查詢)和公用表表達式(CTE)等返回的結果還需要進一步的去使用，加了ORDER BY進行排序是多此一舉，反而會浪費系統資源。所以數據庫的開發者不希望大家使用這樣不規范操作。

所以下次就不要在表表達式里添加ORDER BY了。

五、T-SQL中表表達式加了TOP可以使用ORDER BY

我們從第四點的報錯信息中可以看到：在另外還指定了 TOP、OFFSET 或 FOR XML是可以使用ORDER BY的。

這又是為什么呢？

我們還是先舉個栗子給大家看一下

SELECT   客戶ID AS ID,  姓名 AS Name,  地址 AS Address,  城市 AS City  FROM  (SELECT TOP 3 *  FROM Customers  ORDER BY 城市) Customers  ORDER BY ID,Name,Address

結果如下：

因為T-SQL中帶有ORDER BY的表表達式加了TOP后返回的是一個沒有固定順序的表。因此，在這種情況下，ORDER BY子句只是為TOP選項定義邏輯順序，就是下面這個邏輯子句

SELECT TOP 3 *  FROM Customers  ORDER BY 城市

結果如下：

而不保證結果集的排列順序，因為表表達式外面至少還有一層才是我們最終需要的結果集。

這里的ORDER BY只對當前的子查詢生效，到了主查詢是不起作用的。必須在主查詢末尾繼續添加一個ORDER BY子句才能對結果集生效，就像我們例子中寫的那樣。

除非邏輯要求，一般情況下并不推薦大家這樣巧妙的避開子查詢中不能使用ORDER BY的限制。

看完上述內容，你們對ORDER BY的用法都有哪些有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。