SQL Server 查詢指令 (8)

基本介紹

教學目標

初步了解 SQL Server 中通用資料表運算式的基本概念。

重點概念

首先我們可以使用通用資料表運算式 (Common Table Expression,CTE) 儲存暫存結果集,並且建立和可用於 T-SQL 陳述式的執行範圍內,包括 SELECT、INSERT、UPDATE、DELETE 和 CREATE VIEW 等陳述式的執行範圍內。此外使用通用資料表運算式的優點主要有改善複雜查詢指令的可讀性和的容易進行維護,我們主要是將複雜查詢分割至不同的單一區塊,接著再由許多區塊建立更複雜的查詢,像是透過通用資料表運算式指向本身的參考進行遞迴查詢。

接著通用資料表運算式還能夠被使用於使用者定義程序,像是函數、預儲程序、觸發事件和檢視表。以及我們可以在 CREATE VIEW 陳述式中將 CTE 指定為用來定義檢視的 SELECT 陳述式之一部份,並且定義多個 CTE 查詢定義,並且透過 UNION、ALL、UNION、INTERSECT 或 EXCEPT 運算子進行組合。

再來當我們定義遞迴通用資料表運算式,則必須包括至少兩個通用資料表運算式查詢定義,錨點成員和遞迴成員各一個,所有錨點成員查詢定義皆必須放在第一個遞迴成員定義的前面,除非通用資料表運算式查詢定義參考本身。組合不正確的遞迴通用資料表運算式可能會造成無限迴圈,若要防止無限迴圈,我們可以在 INSERT、UPDATE、DELETE 或 SELECT 陳述式的 OPTION 子句中使用 MAXRECURSION 提示,其主要是限制特定陳述式所能使用的遞迴等級數目,以利我們控制陳述式的執行,直到解決問題為止,預設值為 100,當指定為 0 時不會有任何限制條件,至於值範圍則在 0 至 32,767 之間。

最後在非遞迴通用資料表運算式中不允許使用以下子句,分別為 ORDER BY (除非指定 TOP 子句)、INTO、OPTION 和 FOR BROWSE,此外在非遞迴通用資料表運算式中不允許使用以下子句,分別為 SELECT DISTINCT、GROUP BY、PIVOT、HAVING、TOP、LEFT、RIGHT、OUTER JOIN、純量彙總、子查詢等。至於通用資料表運算式則通常是以 WITH 子句開始定義,並且在查詢中重複使用和遞迴使用,以利提高 T-SQL 程式碼的可讀性和可維護性。若我們需要簡化邏輯上的事情,或者使用遞迴作業,則通用資料表運算式將會是首選,請注意通用資料表運算式沒有專門執行統計資料的功能,若要執行統計資料的功能則要使用衍生資料表。

通用資料表運算式語法

1
2
3
4
5
[ WITH <通用資料表運算式> [ ,...n ] ]  
<通用資料表運算式>::=
表達式名稱 [ ( 欄位名稱 [ ,...n ] ) ]
AS
( 通用資料表運算式之查詢定義 )

相關資源