愛華網(wǎng)
SELECT* FROM locs order by loclevel
使用公用表表達(dá)式的遞歸查詢
SQL Server 2008 R2其他版本公用表表達(dá)式 (CTE) 具有一個重要的優(yōu)點，那就是能夠引用其自身，從而創(chuàng)建遞歸 CTE。遞歸 CTE 是一個重復(fù)執(zhí)行初始 CTE以返回數(shù)據(jù)子集直到獲取完整結(jié)果集的公用表表達(dá)式。
當(dāng)某個查詢引用遞歸 CTE時，它即被稱為遞歸查詢。遞歸查詢通常用于返回分層數(shù)據(jù)，例如：顯示某個組織圖中的雇員或物料清單方案（其中父級產(chǎn)品有一個或多個組件，而那些組件可能還有子組件，或者是其他父級產(chǎn)品的組件）中的數(shù)據(jù)。
遞歸 CTE 可以極大地簡化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW語句中運行遞歸查詢所需的代碼。在 SQL Server的早期版本中，遞歸查詢通常需要使用臨時表、游標(biāo)和邏輯來控制遞歸步驟流。有關(guān)公用表表達(dá)式的詳細(xì)信息，請參閱使用公用表表達(dá)式。
遞歸CTE 的結(jié)構(gòu)Transact-SQL 中的遞歸 CTE的結(jié)構(gòu)與其他編程語言中的遞歸例程相似。盡管其他語言中的遞歸例程返回標(biāo)量值，但遞歸 CTE 可以返回多行。
遞歸 CTE 由下列三個元素組成：
例程的調(diào)用。
遞歸 CTE 的第一個調(diào)用包括一個或多個由 UNION ALL、UNION、EXCEPT 或 INTERSECT運算符聯(lián)接的CTE_query_definitions。由于這些查詢定義形成了CTE 結(jié)構(gòu)的基準(zhǔn)結(jié)果集，所以它們被稱為“定位點成員”。
CTE_query_definitions被視為定位點成員，除非它們引用了CTE 本身。所有定位點成員查詢定義必須放置在第一個遞歸成員定義之前，而且必須使用 UNION ALL運算符聯(lián)接最后一個定位點成員和第一個遞歸成員。
例程的遞歸調(diào)用。
遞歸調(diào)用包括一個或多個由引用 CTE 本身的 UNION ALL運算符聯(lián)接的CTE_query_definitions。這些查詢定義被稱為“遞歸成員”。
終止檢查。
終止檢查是隱式的；當(dāng)上一個調(diào)用中未返回行時，遞歸將停止。
| 注意 |
|---|
如果遞歸 CTE組合不正確，可能會導(dǎo)致無限循環(huán)。例如，如果遞歸成員查詢定義對父列和子列返回相同的值，則會造成無限循環(huán)。在測試遞歸查詢的結(jié)果時，可以通過在INSERT、UPDATE、DELETE 或 SELECT 語句的 OPTION 子句中使用 MAXRECURSION 提示和 0 到32,767 之間的值，來限制特定語句允許的遞歸級數(shù)。有關(guān)詳細(xì)信息，請參閱查詢提示(Transact-SQL)和WITHcommon_table_expression_r(Transact-SQL)。 |
偽代碼和語義
遞歸 CTE 結(jié)構(gòu)必須至少包含一個定位點成員和一個遞歸成員。以下偽代碼顯示了包含一個定位點成員和一個遞歸成員的簡單遞歸 CTE的組件。
WITHcte_name ( column_name [,...n] )
AS
(
CTE_query_definition–- Anchor member is defined.
UNIONALL
CTE_query_definition–- Recursive member is defined referencing cte_name.
)
--Statement using the CTE
SELECT*
FROMcte_name
遞歸執(zhí)行的語義如下：
將 CTE 表達(dá)式拆分為定位點成員和遞歸成員。
運行定位點成員，創(chuàng)建第一個調(diào)用或基準(zhǔn)結(jié)果集 (T0)。
運行遞歸成員，將 Ti作為輸入，將Ti+1作為輸出。
重復(fù)步驟 3，直到返回空集。
返回結(jié)果集。這是對 T0到Tn執(zhí)行UNIONALL 的結(jié)果。
以下示例通過返回 Adventure WorksCycles 公司的雇員的分層列表（從最高級雇員開始）顯示遞歸 CTE 結(jié)構(gòu)的語義。示例后面是代碼執(zhí)行的演練。
-- Create an Employee table. CREATE TABLE dbo.MyEmployees ( EmployeeID smallint NOT NULL, FirstName nvarchar(30) NOT NULL, LastName nvarchar(40) NOT NULL, Title nvarchar(50) NOT NULL, DeptID smallint NOT NULL, ManagerID int NULL, CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) ); -- Populate the table with values. INSERT INTO dbo.MyEmployees VALUES (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL) ,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1) ,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273) ,(275, N'Michael', N'Blythe', N'Sales Representative',3,274) ,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274) ,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273) ,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285) ,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273) ,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);
USE AdventureWorks2008R2; GO WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level) AS ( -- Anchor member definition SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 0 AS Level FROM dbo.MyEmployees AS e INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL WHERE ManagerID IS NULL UNION ALL -- Recursive member definition SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, Level + 1 FROM dbo.MyEmployees AS e INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL INNER JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) -- Statement that executes the CTE SELECT ManagerID, EmployeeID, Title, DeptID, Level FROM DirectReports INNER JOIN HumanResources.Department AS dp ON DirectReports.DeptID = dp.DepartmentID WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0; GO
示例代碼演練
遞歸 CTEDirectReports定義了一個定位點成員和一個遞歸成員。
定位點成員返回基準(zhǔn)結(jié)果集 T0。這就是公司中的最高級雇員，即不向經(jīng)理報告的雇員。
以下是定位點成員返回的結(jié)果集：
ManagerIDEmployeeIDTitleLevel ------------------------------------------------------ NULL1Chief Executive Officer0
遞歸成員返回定位點成員結(jié)果集中的雇員的直接下屬。這是通過在Employee表和DirectReportsCTE之間執(zhí)行聯(lián)接操作獲得的。正是此次對 CTE 自身的引用建立了遞歸調(diào)用。利用CTEDirectReports中的雇員作為輸入(Ti)，聯(lián)接(MyEmployees.ManagerID= DirectReports.EmployeeID) 返回經(jīng)理為 (Ti)的雇員作為輸出 (Ti+1)。這樣，遞歸成員的第一次迭代返回了以下結(jié)果集：
ManagerIDEmployeeIDTitleLevel ------------------------------------------------------ 1273Vice President of Sales1
重復(fù)激活遞歸成員。遞歸成員的第二次迭代使用步驟 3中的單行結(jié)果集（包含EmployeeID273）作為輸入值，并返回以下結(jié)果集：
ManagerIDEmployeeIDTitleLevel ------------------------------------------------------ 27316Marketing Manager2 273274North American Sales Manager2 273285Pacific Sales Manager2
遞歸成員的第三次迭代使用上面的結(jié)果集作為輸入值，并返回以下結(jié)果集：
ManagerIDEmployeeIDTitleLevel ------------------------------------------------------ 1623Marketing Specialist3 274275Sales Representative3 274276Sales Representative3 285286Sales Representative3
正在運行的查詢返回的最終結(jié)果集是定位點成員和遞歸成員生成的所有結(jié)果集的并集。
以下是示例返回的完整結(jié)果集：
ManagerIDEmployeeIDTitleLevel ------------------------------------------------------ NULL1Chief Executive Officer0 1273Vice President of Sales1 27316Marketing Manager2 273274North American Sales Manager2 273285Pacific Sales Manager2 1623Marketing Specialist3 274275Sales Representative3 274276Sales Representative3 285286Sales Representative3
