|
所謂的一般資料表運算式(CTE)其實與檢視或是衍生查詢十分類似, 讓您在執行SELECT、INSERT、UPDATE或DELETE等查詢時,
可以拿來做參考之用。但是CTE與衍生查詢的不同之處在於, 您不需要在每一次使用到查詢語句的時候都重新輸入一次。您也可以在定義CTE時使用區域變數—這是在定義檢視(view)時無法做到的。
CTE的基本語法是：
WITH expression_name [ ( column_name [ ,...n ] ) ]
AS ( CTE_query_definition ) |
CTE的引數如表1-7所述。
表1-7 CTE的引數
| 引數 |
說明 |
| expression_name |
指定一般資料表運算式的名稱。 |
| column_name [ ,...n ] |
運算式中不得重複的資料欄名稱。 |
| CTE_query_definition |
定義CTE的SELECT查詢語句。 |
非遞迴式CTE是一種在使用時不會參照到本身的語句。其作用近似於暫存的查詢所得結果。遞迴式CTE的定義方式與非遞迴式CTE大致相同,
只不過遞迴式CTE傳回的是與自身相關的層級化資料。用CTE來代表遞迴式的資料, 可比其他作法省下可觀的程式碼。
以下兩個應用技巧使用AdventureWorks資料庫, 告訴您非遞迴式與遞迴式CTE的用法。
Adventure資料庫是SQL Server 2005所附的範例資料庫。它與先前舊版SQL Server的Northwind及Pubs資料庫相仿。關於安裝該資料庫的指示,
請參照SQL Server 2005線上手冊的 「執行安裝程式來安裝 AdventureWorks 範例資料庫和範例」 。
非遞迴式CTE的運用
以下這個CTE的範例會展現如何從Purchasing.Vendor資料表傳回供應商資料—分別是前5名與最後5名, 並依照名稱排序：
WITH VendorSearch (RowNumber, VendorName, AccountNumber)
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY Name) RowNum,
Name,
AccountNumber
FROM Purchasing.Vendor
)
SELECT RowNumber,
VendorName,
AccountNumber
FROM VendorSearch
WHERE RowNumber BETWEEN 1 AND 5
UNION
SELECT RowNumber,
VendorName,
AccountNumber
FROM VendorSearch
WHERE RowNumber BETWEEN 100 AND 104
|
傳回的結果是：
RowNumber VendorName AccountNumber
----------- ----------------------------- ------------
1 A. Datum Corporation ADATUM0001
2 Advanced Bicycles ADVANCED0001
3 Allenson Cycles ALLENSON0001
4 American Bicycles and Wheels AMERICAN0001
5 American Bikes AMERICAN0002
100 Vista Road Bikes VISTARO0001
101 West Junction Cycles WESTJUN0001
102 WestAmerica Bicycle Co. WESTAMER0001
103 Wide World Importers WIDEWOR0001
104 Wood Fitness WOODFIT0001
(10個資料列受到影響)
|
以上的例子只採用了UNION, 但是非遞迴式CTE的能耐可不止如此而已, 它的用法可以比照一般的SELECT查詢：
WITH VendorSearch (VendorID, VendorName)
AS
(
SELECT VendorID,
Name
FROM Purchasing.Vendor
)
SELECT v.VendorID,
v.VendorName,
p.ProductID,
p.StandardPrice
FROM VendorSearch v
INNER JOIN Purchasing.ProductVendor p ON
v.VendorID = p.VendorID
ORDER BY v.VendorName
|
傳回的結果(摘錄)如下：
VendorID VendorName ProductID StandardPrice
----------- ----------------------- ----------- ---------
32 Advanced Bicycles 359 45.41
32 Advanced Bicycles 360 43.41
32 Advanced Bicycles 361 47.48
...
91 WestAmerica Bicycle Co. 363 41.26
28 Wide World Importers 402 45.21
57 Wood Fitness 2 39.92
(406個資料列受到影響)
|
運作原理
在第一個例子裡, 我們用WITH定義了CTE的名稱以及它所包含的欄位。由於這個CTE的資料並未與自身進行聯結, 因此屬於非遞迴式CTE。上例僅僅用了UNION,
將來自CTE的兩個資料集做聯集：
|
WITH VendorSearch (RowNumber, VendorName, AccountNumber)
|
在CTE中定義的欄位名稱可以對應到查詢語句中的實際欄位名稱—或自行產生別名亦可。以本例來說, Purchasing.Vendor裡的Name欄位,
就會對應到CTE裡的VendorName。
接下來, AS標示出定義CTE查詢的起點：
在左括弧以下, 查詢語句使用了一項SQL Server 2005的新函數, 它會傳回查詢結果的資料列序號—並依照供應商名稱排序：
SELECT ROW_NUMBER() OVER (ORDER BY Name) RowNum,
Name,
AccountNumber
FROM Purchasing.Vendor)
|
除了供應商名稱, 還會同時傳回Purchasing.Vendor資料表裡的AccountNumber。最後以右括弧標示出CTE定義敘述的終點。
緊接在CTE定義之後的, 就是實際使用CTE的查詢語句。請記住, 凡是需要參照到CTE所定義欄位的敘述, 不論是SELECT、INSERT、UPDATE還是DELETE,
都必須完全按照CTE裡的定義：
SELECT RowNumber,
VendorName,
AccountNumber
FROM VendorSearch
WHERE RowNumber BETWEEN 1 AND 5
|
SELECT所取出的欄位名稱完全來自剛剛定義過的VendorSearch CTE。而在WHERE子句裡,
要求傳回的是第1至第5列資料。緊接著在第二段查詢開始之前, 是UNION運算子：
第二段查詢顯示的是最後5列資料。也就是說, VendorSearch這個CTE前後使用了兩次—不過我們總共只做了一次CTE定義(不像衍生查詢,
每次引用都要原封不動地再鍵入一段)—因此程式碼得以精簡。
在第二個應用技巧裡, 我們改定義了一個簡單的CTE, 其中並未使用任何函數, 只引用了兩個來自Purchasing.Vendor資料表的欄位—VendorID與VendorName：
WITH VendorSearch (VendorID, VendorName)
AS
(
SELECT VendorID,
Name
FROM Purchasing.Vendor)
|
緊接在CTE定義之後的查詢語句, 將剛剛才定義好的CTE 「VendorSearch」
視為一般資料表(只差沒指定屬於哪一個schema), 並與其他資料表聯結：
SELECT v.VendorID,
v.VendorName,
p.ProductID,
p.StandardPrice
FROM VendorSearch v
INNER JOIN Purchasing.ProductVendor p ON
v.VendorID = p.VendorID
ORDER BY v.VendorName
|
要注意的事只有一件—如果CTE是批次敘述的一部分, 那麼在CTE開始定義之前的敘述, 必須加上一個分號做區隔。
|
PSP 無所不玩2nd Evolution 超進化版
