ORDER BY的用法都有哪些

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

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

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

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

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

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

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

Student(ID,Name,Age)

Student集合

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

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

Student(ID,Name,Age)

Student對(duì)象

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

注：markdown對(duì)代碼塊的語(yǔ)法是開(kāi)始和結(jié)束行都要添加：```,其中 ` 為windows鍵盤(pán)左上角那

這里涉及SQL語(yǔ)句的語(yǔ)法順序和執(zhí)行順序了，我們常見(jiàn)的SQL語(yǔ)法順序如下： 

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ù)庫(kù)引擎在執(zhí)行SQL語(yǔ)句并不是從SELECT開(kāi)始執(zhí)行，而是從FROM開(kāi)始，具體執(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后才開(kāi)始執(zhí)行，這個(gè)時(shí)候SELECT后面列的別名只對(duì)后續(xù)的步驟生效，而對(duì)SELECT前面的步驟是無(wú)效的。所以如果你在WHERE，GROUP BY，或HAVING后面使用列的別名均會(huì)報(bào)錯(cuò)。

我們舉例測(cè)試一下。

示例表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識(shí)別，所以才會(huì)報(bào)“列名'City'無(wú)效”的提示。

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

3、測(cè)試GROUP BY后使用列別名

SELECT   城市 AS City  FROM Customers  GROUP BY City

結(jié)果如下：

4、測(cè)試HAVING后使用列別名

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

結(jié)果如下：

5、測(cè)試ORDER BY后面使用列別名

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

結(jié)果如下：

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

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

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

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

結(jié)果如下：

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

可是當(dāng)查詢(xún)的列發(fā)生改變，忘了修改ORDER BY列表。特別是當(dāng)查詢(xún)語(yǔ)句很長(zhǎng)時(shí)，要找到ORDER BY與SELECT列表中的哪個(gè)列相對(duì)應(yīng)會(huì)非常困難。

例如：

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

由于增加了一列“客戶ID”，原本的題意還是對(duì)Name，Address，City排序，但是因?yàn)槭褂昧薕RDER BY加數(shù)字，排序后的結(jié)果如下：

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

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

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

例如下面的視圖是無(wú)效的

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

結(jié)果如下：

這個(gè)錯(cuò)誤是不是很熟悉？因?yàn)楹芏嘈』锇榻?jīng)常喜歡在視圖或子查詢(xún)里面加ORDER BY，然后一執(zhí)行就會(huì)報(bào)這個(gè)錯(cuò)。

根本原因不敢妄加斷定，因?yàn)樗褜ち撕芏辔墨I(xiàn)資料也沒(méi)給出一個(gè)具體的說(shuō)法。

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

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

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

我們從第四點(diǎn)的報(bào)錯(cuò)信息中可以看到：在另外還指定了 TOP、OFFSET 或 FOR XML是可以使用ORDER BY的。

這又是為什么呢？

我們還是先舉個(gè)栗子給大家看一下

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é)果如下：

因?yàn)門(mén)-SQL中帶有ORDER BY的表表達(dá)式加了TOP后返回的是一個(gè)沒(méi)有固定順序的表。因此，在這種情況下，ORDER BY子句只是為T(mén)OP選項(xiàng)定義邏輯順序，就是下面這個(gè)邏輯子句

SELECT TOP 3 *  FROM Customers  ORDER BY 城市

結(jié)果如下：

而不保證結(jié)果集的排列順序，因?yàn)楸肀磉_(dá)式外面至少還有一層才是我們最終需要的結(jié)果集。

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

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

看完上述內(nèi)容，你們對(duì)ORDER BY的用法都有哪些有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。