SQL Server 2005 Beta 2 Transact-SQL功能（2）

　　單父節點環境雇員組織結構圖
　　
　　對於單父節點層次結構方案使用雇員組織結構圖
　　
　　注 本節中的示例使用一個名為 Employees 的表該表具有與 AdventureWorks 中的 HumanResourcesEmployee 表不同的結構您應當在自己的測試數據庫或 tempdb 中運行代碼而不要在 AdventureWorks 中運行代碼
　　
　　以下代碼生成 Employees 表並且用示例數據填充它
　　
　　USE tempdb or your own test database
　　CREATE TABLE Employees
　　(
　　　empid　 int　　　　 NOT NULL
　　　mgrid　 int　　　　 NULL
　　　empname varchar() NOT NULL
　　　salary　money　　　 NOT NULL
　　　CONSTRAINT PK_Employees PRIMARY KEY(empid)
　　　CONSTRAINT FK_Employees_mgrid_empid
　　FOREIGN KEY(mgrid)
　　REFERENCES Employees(empid)
　　)
　　CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
　　SET NOCOUNT ON
　　INSERT INTO Employees VALUES( NULL Nancy　 $)
　　INSERT INTO Employees VALUES( 　 Andrew　 $)
　　INSERT INTO Employees VALUES( 　 Janet　 $)
　　INSERT INTO Employees VALUES( 　 Margaret $)
　　INSERT INTO Employees VALUES( 　 Steven　 $)
　　INSERT INTO Employees VALUES( 　 Michael $)
　　INSERT INTO Employees VALUES( 　 Robert　 $)
　　INSERT INTO Employees VALUES( 　 Laura　 $)
　　INSERT INTO Employees VALUES( 　 Ann　　 $)
　　INSERT INTO Employees VALUES( 　 Ina　　 $)
　　INSERT INTO Employees VALUES( 　 David　 $)
　　INSERT INTO Employees VALUES( 　 Ron　　 $)
　　INSERT INTO Employees VALUES( 　 Dan　　 $)
　　INSERT INTO Employees VALUES( 　 James　 $)
　　
　　每個雇員都向其 ID 存儲在 mgrid 列中的經理匯報在引用 empid 列的 mgrid 列上定義了一個外鍵這意味著經理 ID 必須對應於該表中的一個有效雇員 ID 或者為 NULL老板 Nancy 在 mgrid 列中具有 NULL 值經理雇員關系如圖 所示
　　　
　　圖 雇員組織結構圖
　　
　　下面是一些可能在 Employees 表上運行的常見請求
　　
　　顯示有關 Robert (empid=) 及其所有級別下屬的詳細信息
　　
　　顯示有關比 Janet (empid=) 低兩個級別的所有雇員的詳細信息
　　
　　顯示通向 James (empid=) 的管理鏈
　　
　　顯示有多少個雇員直接或間接向每個經理匯報
　　
　　以適當的方式顯示所有雇員以便可以容易地查看他們的層次依賴項
　　
　　遞歸 CTE 提供了處理上述請求（它們在本質上是遞歸的）的手段而無須在數據庫中維護有關層次結構的其他信息
　　
　　第一個請求可能是最常見的一個請求返回某個雇員（例如empid= 的 Robert）及其所有級別的下屬以下 CTE 提供了對該請求的解決方案
　　
　　WITH EmpCTE(empid empname mgrid lvl)
　　AS
　　(
　　
　　　 Anchor Member (AM)
　　　SELECT empid empname mgrid
　　　FROM Employees
　　　WHERE empid =
　　　UNION ALL
　　
　　　 Recursive Member (RM)
　　　SELECT Eempid Eempname Emgrid Mlvl+
　　　FROM Employees AS E
　　JOIN EmpCTE AS M
　　　ON Emgrid = Mempid
　　)
　　SELECT * FROM EmpCTE
　　
　　以下為結果集
　　
　　empid　　　 empname　　　　　　　　　 mgrid　　　 lvl
　　
　　　　　　　 Robert　　　　　　　　　　　　　　　
　　　　　　　David　　　　　　　　　　 　　　　　
　　　　　　　Ron　　　　　　　　　　　 　　　　　
　　　　　　　Dan　　　　　　　　　　　 　　　　　
　　　　　　　James　　　　　　　　　　 　　　　　
　　
　　按照先前描述的遞歸 CTE 邏輯該 CTE 被按如下方式處理
　　
　　錨定成員被激活並且從 Employees 表中返回 Robert 的行請注意在 lvl 結果列中返回的常量
　　
　　 遞歸成員被反復激活並且借助於 Employees 和 EmpCTE 之間的聯接操作返回上一個結果的直接下屬Employees 代表下屬而 EmpCTE（它包含上一個調用的結果）代表經理
　　
　　首先Robert 的下屬被返回DavidRon 和 James
　　
　　然後DavidRon 和 Dan 的下屬被返回只有 James
　　
　　最後James 的下屬被返回無 — 在這種情況下遞歸終止
　　
　　 外部查詢從 EmpCTE 中返回所有行
　　
　　請注意對於每個遞歸調用lvl 值反復遞增
　　
　　使用該級別計數器您可以限制遞歸中的迭代次數例如以下 CTE 用來返回比 Janet 低兩個級別的所有雇員
　　
　　WITH EmpCTEJanet(empid empname mgrid lvl)
　　AS
　　(
　　　SELECT empid empname mgrid
　　　FROM Employees
　　　WHERE empid =
　　　UNION ALL
　　
　　　SELECT Eempid Eempname Emgrid Mlvl+
　　　FROM Employees as E
　　JOIN EmpCTEJanet as M
　　　ON Emgrid = Mempid
　　　WHERE lvl <
　　)
　　SELECT empid empname
　　FROM EmpCTEJanet
　　WHERE lvl =
　　
　　以下為結果集
　　
　　empid　　　 empname
　　
　　　　　　　David
　　　　　　　Ron
　　　　　　　Dan
　　
　　該代碼示例中比上一個代碼示例增加的代碼以粗體顯示遞歸成員中的篩選器 WHERE lvl < 被用作遞歸終止檢查 — 當 lvl = 時不會返回任何行因而遞歸停止外部查詢中的篩選器 WHERE lvl = 用來移除上至級別 的所有級別請注意從邏輯上講外部查詢中的篩選器 (lvl = ) 本身就足以只返回所需的行遞歸成員中的篩選器 (lvl < ) 是出於性能原因而添加的 — 為了在返回 Janet 下的兩個級別之後立即停止遞歸
　　
　　正如前面提到的那樣CTE 可以引用在同一批處理中定義的本地變量例如為了使查詢更一般化您可以使用變量而不是常量作為雇員 ID 和級別
　　
　　DECLARE @empid AS INT @lvl AS INT
　　SET @empid = Janet
　　SET @lvl　 = two levels
　　WITH EmpCTE(empid empname mgrid lvl)
　　AS
　　(
　　　SELECT empid empname mgrid
　　　FROM Employees
　　　WHERE empid = @empid
　　　UNION ALL
　　
　　　SELECT Eempid Eempname Emgrid Mlvl+
　　　FROM Employees as E
　　JOIN EmpCTE as M
　　　ON Emgrid = Mempid
　　　WHERE lvl < @lvl
　　)
　　SELECT empid empname
　　FROM EmpCTE
　　WHERE lvl = @lvl
　　
　　您可以使用提示在已經調用一定數量的遞歸迭代之後強行終止查詢可以通過在外部查詢的結尾添加 OPTION(MAXRECURSION value) 做到這一點如以下示例所示
　　
　　WITH EmpCTE(empid empname mgrid lvl)
　　AS
　　(
　　　SELECT empid empname mgrid
　　　FROM Employees
　　　WHERE empid =
　　　UNION ALL
　　
　　　SELECT Eempid Eempname Emgrid Mlvl+
　　　FROM Employees as E
　　JOIN EmpCTE as M
　　　ON Emgrid = Mempid
　　)
　　SELECT * FROM EmpCTE
　　OPTION (MAXRECURSION )
　　
　　以下為結果集
　　
　　empid　　　 empname　　　　　　　　　 mgrid　　　 lvl
　　
　　　　　　　 Nancy　　　　　　　　　　 NULL　　　　
　　　　　　　 Andrew　　　　　　　　　　　　　　　
　　　　　　　 Janet　　　　　　　　　　 　　　　　
　　　　　　　 Margaret　　　　　　　　　　　　　　
　　　　　　　Ina　　　　　　　　　　　 　　　　　
　　　　　　　 Robert　　　　　　　　　　　　　　　
　　　　　　　 Laura　　　　　　　　　　 　　　　　
　　　　　　　 Ann　　　　　　　　　　　 　　　　　
　　Net SqlClient Data Provider: Msg Level State Line
　　Statement terminated Maximum recursion has been exhausted before statement completion
　　
　　可能返回（但是不能保證返回）迄今生成的結果並且生成錯誤 您可能會想到使用 MAXRECURSION 選項實現以下請求使用 MAXRECURSION 提示而不是遞歸成員中的篩選器返回 Janet 下兩個級別的雇員
　　
　　WITH EmpCTE(empid empname mgrid lvl)
　　AS
　　(
　　　SELECT empid empname mgrid
　　　FROM Employees
　　　WHERE empid =
　　　UNION
From:http://tw.wingwit.com/Article/program/SQLServer/201311/21971.html