詳解SQL Server 2008中CTE遞歸查詢的實現
今天基本搞清楚SQL Server中遞歸查詢的實現方式，So，先記錄下來。不過呢，個人覺得SQL Server的遞歸查詢相對于Oracle中的遞歸查詢更加難以理解。
從SQL Server 2005開始，我們可以直接通過CTE來支持遞歸查詢，這對查詢樹形或層次結構的數據很有用。CTE即公用表表達式，雖然不恰當，但你可以將它看做成一個臨時命名的結果集合。
我們先建立一個示例表，名稱為MENU，表示菜單的層次結構:
- CREATE TABLE MENU
- (
- name nvarchar(50) NOT NULL PRIMARY KEY,
- senior nvarchar(50) NULL
- );
- INSERT INTO MENU values
- ('文件',NULL),
- ('新建','文件'),
- ('項目','新建'),
- ('使用當前連接查詢','新建');
表示的菜單層次如下：
- 文件
- 新建
- 項目
- 使用當前連接查詢
- OK，先看CTE的語法：
- WITH CTE名稱(字段列表)
- AS
- (
- 查詢語句
- )
例如：
- WITH lmenu(name,senior)
- as
- (
- SELECT name,senior from menu
- )
我們定義了一個名稱為lmenu的CTE，這樣我們后續即可直接使用lmenu來查詢，如：
- SELECT * FROM lmenu
如果我們在定義CTE的查詢語句中直接引用CTE表本身，則會形成遞歸查詢，當然遞歸查詢具有自己的特殊結構，下面的SQL通過遞歸查詢獲取每個菜單的層次深度：
- WITH lmenu(name,senior,level) as
- (
- SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL
- UNION ALL
- SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
- where a.senior = b.name
- )
- SELECT * from lmenu
結果：
- name senior level
- ----------------------------------- -----------
- 文件 NULL 0
- 新建 文件 1
- 使用當前連接查詢 新建 2
- 項目 新建 2
注意查詢定義語句，它由兩條查詢語句構成，其中
- SELECT NAME,SENIOR,0 level FROM MENU WHERE SENIOR IS NULL
稱為定位成員，SQL Server通過此語句來判斷是否繼續進行遞歸。
- SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
- where a.senior = b.name
稱之為遞歸成員，其特征為from子句中引用了CTE對象自身。
遞歸CTE具有一些限制條件(引自MSDN)：
至少有一個定位點成員和一個遞歸成員，當然，你可以定義多個定位點成員和遞歸成員，但所有定位點成員必須在遞歸成員的前面
定位點成員之間必須使用UNION ALL、UNION、INTERSECT、EXCEPT集合運算符，最后一個定位點成員與遞歸成員之間必須使用UNION ALL，遞歸成員之間也必須使用UNION ALL連接
定位點成員和遞歸成員中的字段數量和類型必須完全一致
遞歸成員的FROM子句只能引用一次CTE對象
遞歸成員中不允許出現下列項
- SELECT DISTINCT
- GROUP BY
- HAVING
- 標量聚合
- TOP
- LEFT、RIGHT、OUTER JOIN（允許出現 INNER JOIN）
- 子查詢
CTE遞歸查詢的執行方式：
遞歸的終止依賴于定位點成員的，如果理解了這一點，也就理解了遞歸查詢的執行方式。
我們來看上例的執行執行過程：
- SELECT * FROM lmenu
這條語句進入遞歸查詢
- SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
- where a.senior = b.name
作為最外層的語句，顯然遞歸的第一層應該根據MENU表的記錄來循環(如果查詢執行計劃，這表示一個嵌套循環)，假設menu表中查詢出的記錄順序如下：
- name senior
- --- --------------------------------------------------
- 文件 NULL
- 新建 文件
- 使用當前連接查詢 新建
- 項目 新建
第一條記錄：
首先判斷是否進入遞歸，由于 文件包含在定位點成員結果集中，不符合遞歸條件，所以不進入遞歸，直接返回從定位點成員集合中返回記錄：
- select name,senior,0 level from menu where senior is null and name='文件'
- name senior level
- --------------- ------------------------ -----------
- 文件 NULL 0
第二條記錄：
即NAME = '新建', 定位點成員結果集中沒有該記錄，將進入遞歸：
將當前行的值帶入遞歸成員：
- SELECT A.NAME,A.SENIOR,b.level+1 level FROM MENU A,lmenu b
- where a.senior = b.name
- AND a.senior = '文件'
- AND a.name='新建'
由于遞歸的關聯條件是a.senior = b.name，所以b.name='文件'，以此條件進入下級遞歸,這實際上就是第一條記錄的情況，由于name='文件'符合定位點條件，所以將終止遞歸，如果我們用子查詢來替換掉lmenu遞歸成員，第二條記錄的查詢語句實際為：
- SELECT a.name,a.senior,b.level+1 from menu a, (
- select name,senior,0 level from menu
- where senior is null and name='文件'
- ) b
- where a.senior=b.name
- and a.senior = '文件'
- and a.name='新建'
name senior level
--------------------- --------------------------
新建 文件 1
第三條記錄：
NAME='使用當前連接查詢'，同樣不符合定位點條件，將進入遞歸：
- SELECT A.NAME,A.SENIOR,b.level+1 FROM MENU A,lmenu b
- where a.senior = b.name
- AND a.senior = '新建'
- AND a.name = '使用當前連接查詢'
同樣，代入當前記錄條件，下級遞歸b.name='新建',由于'新建'還不符合定位點條件，所以還將繼續遞歸，及lmenu b表示子查詢：
- select c.name,c.senior,d.level+1 level from menu c,lmenu d
- where c.senior = d.name
- and c.name = '新建'
- and c.senior = '文件'
替換成上述語句后，d.name='文件'，將再次判斷是否需要繼續遞歸，由于'文件'符合終止遞歸條件，所以將終止遞歸。
我們用子查詢表示第三條記錄的遞歸過程如下：
- SELECT a.name,a.senior,b.level+1 level FROM menu A,(
- select c.name,c.senior,d.level+1 level from menu c,(
- select name,senior,0 level from menu where senior is null and name='文件'
- ) d
- where c.senior = d.name
- and c.name = '新建'
- and c.senior = '文件'
- ) b
- where a.senior = b.name
- and a.senior = '新建'
- and a.name = '使用當前連接查詢'
name senior level
--------------------------------------------------------- -----------
使用當前連接查詢 新建 2
第四條記錄與第三條記錄的遞歸層次完全一樣。
原文標題：SQL Server 2008中的CTE遞歸查詢
鏈接：http://www.cnblogs.com/xfrog/archive/2010/10/10/1847462.html
【編輯推薦】
- SQL Server 2000刪除實戰演習
- SQL Server存儲過程的命名標準如何進行？
- 卸載SQL Server 2005組件的正確順序
- 對SQL Server字符串數據類型的具體描述
- SQL Server數據類型的種類與應用
