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

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

一、ORDER BY返回的是游標(biāo)而不是集合

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

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

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

Student(ID,Name,Age)

Student集合

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

如下圖，經(jīng)過對Student表的ID進(jìn)行ORDER BY排序后，Student表變成了有序?qū)ο?，也就是我們上面說的游標(biāo)。

Student(ID,Name,Age)

Student對象

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

這里涉及SQL語句的語法順序和執(zhí)行順序了，我們常見的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> 

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

(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后才開始執(zhí)行，這個時候SELECT后面列的別名只對后續(xù)的步驟生效，而對SELECT前面的步驟是無效的。所以如果你在WHERE，GROUP BY，或HAVING后面使用列的別名均會報錯。

我們舉例測試一下。

示例表Customers結(jié)構(gòu)及數(shù)據(jù)如下：

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

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

結(jié)果如下：

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

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

執(zhí)行結(jié)果如下：

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

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

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

SELECT 
城市 AS City
FROM Customers
GROUP BY City

結(jié)果如下：

4、測試HAVING后使用列別名

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

結(jié)果如下：

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

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

結(jié)果如下：

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

三、謹(jǐn)慎使用ORDER BY 后面接數(shù)字的方式來進(jìn)行排序

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

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

結(jié)果如下：

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

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

例如：

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

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

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

四、表表達(dá)式不能使用ORDER BY排序

表表達(dá)式包括視圖，內(nèi)聯(lián)表值函數(shù)，派生表(子查詢)和公用表表達(dá)式(CTE)。

例如下面的視圖是無效的。

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

結(jié)果如下：

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

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

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

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

五、T-SQL中表表達(dá)式加了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

結(jié)果如下：

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

SELECT TOP 3 *
FROM Customers
ORDER BY 城市

結(jié)果如下：

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

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

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

以上就是有關(guān)ORDER BY的一些用法，你學(xué)會了嗎？