【原】SQL Server中使用CTE遞迴查詢

 目錄

1. 背景
2. 問題
3. 思路
4. CTE
5. CTE遞迴查詢
6. 結束語
7. 參考資料

背景

        好久未寫博了，最近遇到一個問題：“怎麼遍曆出一個父級菜單下所有子功能表？”小的隨後用CTE遞迴查詢解決此問題，整理記錄下來以作分享。

問題

怎麼遍曆出一個父級菜單下所有子功能表?

思路

• 用CTE遞迴查詢解決
• 寫一個自訂函數/預存程序迭代演算法來實現

CTE

1. 定義
2. 文法結構
3. 使用CTE準則
4. 範例程式碼

定義

CTE（Common Table Expressions）是從SQL Server 2005以後版本才有的。指定的臨時命名結果集，這些結果集稱為CTE。 與派生表類似，不儲存為對象，並且只在查詢期間有效。與派生表的不同之處在於，CTE 可自引用，還可在同一查詢中引用多次。使用CTE能改善代碼可讀性，且不損害其效能。

文法結構

CTE 的基本文法結構如下：

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

只有在查詢定義中為所有結果列都提供了不同的名稱時，列名稱列表才是可選的。

運行 CTE 的語句為：

SELECT <column_list>

FROM expression_name;

使用CTE準則

建立CTE時，需要注意的一些準則，詳見MSND列出的使用準則：

1. 定義CTE時最好加首碼”;”
2. CTE內部定義的欄欄位要保持一致
3. CTE with之後第一句必須使用CTE的select。即CTE的生命週期只是在第一次使用之後就消亡。
4. sp中只能使用一次with語句。
5. 定義多個CTE時，只聲明一個with關鍵字就行，比如

with test1

as

(

select * …………

)，

test2 as

(

select * …………

)

通用表運算式(CTE)是SQL Server的一項新功能。本質上CTE是一個臨時結果集，它僅僅存在於它發生的語句中。可以在SELECT、INSERT、DELETE或CREATE VIEW語句中建立一個CTE

範例程式碼

建立測試表

USE MASTERGOIF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='NODE')DROP TABLE NODE---建立表CREATE TABLE NODE(   NID INT PRIMARY KEY,   PARENTID INT NOT NULL,   NAME  NVARCHAR(50) NOT NULL)

插入資料

INSERT INTO NODE VALUES(1,0,'aa'),(2,1,'bb'),(3,2,'cc'),(4,2,'dd'),(5,3,'ee'),(6,4,'ff'),(7,5,'gg'),(8,4,'hh'),(9,3,'ii'),(10,5,'jj');

建立一個CTE的DEMO

;WITH NodeCTE(NID,PARENTID,NAME)
AS
(
  SELECT NID,PARENTID,NAME FROM NODE WHERE NAME='BB'
UNION ALL
  SELECT B.NID,B.PARENTID,B.NAME FROM NodeCTE A ,NODE B WHERE B.PARENTID=A.NID
)

SELECT * FROM NodeCTE;

CTE遞迴查詢

1. CTE遞迴查詢結構
2. CTE遞迴查詢原理
3. 原節點圖和執行CTE結果集

CTE遞迴查詢結構（引自MSDN）

Transact-SQL 中的遞迴 CTE 的結構與其他程式設計語言中的遞迴常式相似。儘管其他語言中的遞迴常式返回標量值，但遞迴 CTE 可以返回多行。

遞迴 CTE 由下列三個元素組成：

1. 常式的調用。

   遞迴 CTE 的第一個調用包括一個或多個由 UNION ALL、UNION、EXCEPT 或 INTERSECT 運算子聯結的 CTE_query_definitions。由於這些查詢定義形成了 CTE 結構的基準結果集，所以它們被稱為“錨點成員”。

   CTE_query_definitions 被視為錨點成員，除非它們引用了 CTE 本身。所有錨點成員查詢定義必須放置在第一個遞迴成員定義之前，而且必須使用 UNION ALL 運算子聯結最後一個錨點成員和第一個遞迴成員。

2. 常式的遞迴調用。

   遞迴調用包括一個或多個由引用 CTE 本身的 UNION ALL 運算子聯結的 CTE_query_definitions。這些查詢定義被稱為“遞迴成員”。

3. 終止檢查。

   終止檢查是隱式的；當上一個調用中未返回行時，遞迴將停止。

注意

如果遞迴 CTE 組合不正確，可能會導致無限迴圈。例如，如果遞迴成員查詢定義對父列和子列返回相同的值，則會造成無限迴圈。在測試遞迴查詢的結果時，可以通過在 INSERT、UPDATE、DELETE 或 SELECT 語句的 OPTION 子句中使用 MAXRECURSION 提示和 0 到 32,767 之間的值，來限制特定語句允許的遞迴級數。

CTE遞迴查詢原理

1. 將CTE表達示拆分為“錨點成員”和“遞迴成員”
2. 運行錨點成員，執行建立第一個結果集T0
3. 運行遞迴成員時，將前一個結果集作為輸入(Ti)，將Ti+1作為輸出
4. 重複第三步，直到返回空集
5. 返回結果集，通過UNION ALL合并T0 到 Tn的結果

原節點圖和執行CTE結果集

CTE結果集

結束語

本文用CTE遞迴方式解決了我之前的問題，到這就要結束了。由於個人經驗和能力原因，文中若有考慮不周或誤導大家的地方請大牛們不吝指教，我會及時改正，謝謝！

參考資料

1. 中文MSDN庫之使用CTE的遞迴查詢：http://msdn.microsoft.com/zh-cn/library/ms186243(v=SQL.100).aspx
2. 中文MSDN庫之CTE：http://msdn.microsoft.com/zh-cn/library/ms175972(SQL.100).aspx
3. 巧用 DB2 遞迴 SQL:http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1010liush/index.html
4. 資料點: 通用表運算式:http://msdn.microsoft.com/zh-cn/magazine/cc163346.aspx#S2