關於使用CTE(通用資料表運算式)的遞迴查詢

本文轉載：http://www.cnblogs.com/shuangnet/archive/2013/03/22/2975929.html

 

通用資料表運算式 (CTE) 具有一個重要的優點，那就是能夠引用其自身，從而建立遞迴 CTE。遞迴 CTE 是一個重複執行初始 CTE 以返回資料子集直到擷取完整結果集的通用資料表運算式。

　　當某個查詢引用遞迴 CTE 時，它即被稱為遞迴查詢。遞迴查詢通常用於返回分層資料，例如：顯示某個組織圖中的僱員或物料清單方案（其中父級產品有一個或多個組件，而那些組件可能還有子組件，或者是其他父級產品的組件）中的資料。

　　遞迴 CTE 可以極大地簡化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 語句中運行遞迴查詢所需的代碼。在 SQL Server 的早期版本中，遞迴查詢通常需要使用暫存資料表、遊標和邏輯來控制遞迴步驟流。　

CTE 的基本文法結構如下：

　　　　WITH expression_name [ ( column_name [,...n] ) ]　　　　AS　　　　( CTE_query_definition )　　　　--只有在查詢定義中為所有結果列都提供了不同的名稱時，列名稱列表才是可選的。　　　　--運行 CTE 的語句為：　　　　SELECT <column_list> FROM expression_name;

在使用CTE時應注意如下幾點：

CTE後面必須直接跟使用CTE的SQL語句（如select、insert、update等），否則，CTE將失效。如下面的SQL語句將無法正常使用CTE：

　　with　　cr as　　（    　　select * from 表名 where 條件　　 ）　　--select * from person.CountryRegion  --如果加上這句話後面用到cr將報錯 　　select * from cr

2. CTE後面也可以跟其他的CTE，但只能使用一個with，多個CTE中間用逗號（,）分隔，如下面的SQL語句所示：

withcte1 as(    select * from table1 where name like '測試%'),cte2 as(    select * from table2 where id > 20),cte3 as(    select * from table3 where price < 100)select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id

3. 如果CTE的運算式名稱與某個資料表或視圖重名，則緊跟在該CTE後面的SQL語句使用的仍然是CTE，當然，後面的SQL語句使用的就是資料表或視圖。

4. CTE 可以引用自身，也可以引用在同一 WITH 子句中預先定義的 CTE。

5. 不能在 CTE_query_definition 中使用以下子句：

• 　COMPUTE 或 COMPUTE BY
• 　ORDER BY（除非指定了 TOP 子句）
• 　INTO
• 　帶有查詢提示的 OPTION 子句
• 　FOR XML
• 　FOR BROWSE

6. 如果將 CTE 用在屬於批處理的一部分的語句中，那麼在它之前的語句必須以分號結尾，如下面的SQL所示：

declare @s nvarchar(3)set @s = '測試%';  -- 必須加分號witht_tree as(    select * from 表 where 欄位 like @s)select * from  t_tree