《T-SQL查詢》讀書筆記Part 1.邏輯查詢處理知多少

Edison Chou發表於2017-12-09
原文網址 : https://www.cnblogs.com/edisonchou/p/7995513.html
SQL筆記

一、關於T-SQL

　　T-SQL是ANSI和ISO SQL標準的MS SQL擴充套件，其正式名稱為Transact-SQL，但一般程式設計師都稱其為T-SQL。

二、邏輯查詢處理各個階段

2.1 邏輯查詢處理流程總覽

2.2 邏輯查詢處理階段解釋

　　（1）FROM：標識出查詢的來源表，處理表運算子。每個運算子會應用一系列的子階段。eg.在JOIN連線運算中涉及的階段是笛卡爾積、ON篩選器和新增外部行。FROM階段會生成一個虛擬表，這裡暫定為VT1。

    • （1-J1）笛卡爾積：對涉及到的兩個表執行笛卡爾積（交叉聯接），生成虛擬表VT1-J1。
    • （1-J2）ON篩選器：對VT1-J1中的行根據ON子句中出現的謂詞進行篩選。只有讓該謂詞取值為TRUE的行，才能插入到VT1-J2中。
    • （1-J3）新增外部行：如果指定了OUTER JOIN（相對於CROSS JOIN或INNER JOIN），則將保留表（Preserved Table）中沒有找到匹配的行，作為外部行新增到VT1-J2中，生成VT1-J3。

　　（2）WHERE：根據在WHERE子句中出現的謂詞對VT1中的行進行篩選。只有讓謂詞計算結果為TRUE的行，才會插入VT2中。

　　（3）GROUP BY：按照GROUP BY子句中指定的列名列表，將VT2中的行進行分組，生成VT3。最終，每個分組只有一個結果行。

　　（4）HAVING：根據HAVING子句出現的謂詞對VT3中的分組進行篩選。只有讓謂詞計算結果為TRUE的行，才會插入VT4。

　　（5）SELECT：處理SELECT子句中的元素，產生VT5。

    • （5-1）計算表示式：計算SELECT列表中的表示式，生成VT5-1。
    • （5-2）DISTINCT：刪除VT5-1中的重複行，生成VT5-2。
    • （5-3）TOP：根據ORDER BY子句定義的邏輯排序，從VT5-2中選擇前面指定數量或百分比的行，生成VT5-3。

　　（6）ORDER BY：根據ORDER BY子句中指定的列名列表，對VT5-3中的行進行排序，生成遊標VC6。

三、查詢示例詳解

3.1 示例場景

　　假設有兩張表：Customers和Orders，表結構和資料如下：

　　

　　這裡我們要查詢來自Madrid並且訂單數少於3個的客戶，查詢程式碼和結果也如下圖所示：

　　

3.2 各階段解釋

　　（1）FROM階段：

FROM dbo.Customers AS C
  LEFT OUTER JOIN dbo.Orders AS O
    ON C.customerid = O.customerid

　　步驟1-J1=>笛卡爾積

　　這裡先不考慮LEFT OUTER，通過JOIN交叉聯接後形成虛擬表VT1-J1：

　　

　　步驟1-J2=>ON篩選器

　　ON篩選器的作用在於從上一步生成的虛擬表VT1-J1中的所有行中篩選出只有使 C.customerid = O.customerid 為TRUE的那些行，將其輸出到新的虛擬表VT1-J2中。

　　

　　步驟1-J3=>新增外部行

　　這一步只會在外連結（OUTER JOIN）中才會發生。這裡是：Customers AS C LEFT OUTER JOIN Orders AS O，即Customer作為保留表。最終的虛擬表VT1-J3如下：

　　

　　*.這裡Customer作為保留表，所以FISSA雖然沒有滿足ON篩選器，但是也會被新增到虛擬表中。

　　（2）WHERE階段：

WHERE C.city = 'Madrid'

　　在此階段會去掉VT1中客戶為MRPHS的行（因為其cityid不是Madrid），生成如下所示的VT2：

　　

ON和WHERE的區別：WHERE對行的刪除是最終的，而ON對行的刪除並不是，因此步驟1-J3新增外部行時會再新增回來。此外，只有當使用外連線時，ON和WHERE才存在這種邏輯區別。　

　　（3）GROUP BY階段：

GROUP BY C.customerid

　　這一步將VT2中的資料行按組進行重組，得到VT3如下圖所示：

　　

　　（4）HAVING階段：

HAVING COUNT(O.orderid) < 3

　　這一步從VT3中進行篩選，只有使得COUNT(O.orderid)<3邏輯值為TRUE的組，才會進入到VT4。HAVING篩選器是唯一可用於分組資料的篩選器。

　　

這裡沒有使用COUNT(*)是因為在外聯接中，COUNT(*)會把外部行也統計在內，比如會將FISSA的訂單數統計為1，這明顯是錯誤的。　　

　　（5）SELECT階段：

　　步驟5-1=>計算表示式

SELECT C.customerid, COUNT(O.orderid) as numorders

　　得到VT5-1

　　

　　步驟5-2=>應用DISTINCT子句

　　此示例木有DISTINCT子句，故VT5-1沒有變化。

　　步驟5-3=>應用TOP選項

　　TOP選項時T-SQL特有的一項功能，允許指定要返回的行數或百分比。不過，此示例也沒有指定TOP，估計VT5=VT5-1。

　　（6）ORDER BY階段：

ORDER BY numorders

　　這一步將對VT5進行排序，返回遊標VC6。ORDER BY子句也是唯一可以重用SELECT列表中建立的列別名的步驟。

　　

參考資料

　　

　　[美] Itzik Ben-Gan 著，成保棟 譯，《Microsoft SQL Server 2008技術內幕：T-SQL查詢》

 

相關文章