小夥伴們在進行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子句是唯一能重用列別名的一步
這裡涉及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的限制
。
轉自公眾號:SQL資料庫開發