نحوه عملکرد Recursive CTE [بایگانی]

tooraj_azizi_1035

یک شنبه 27 مرداد 1392, 14:14 عصر

CTE بازگشتی نوع خاصی از CTE هست که تکرار اجرا رو تا زمانی که Result Set خالی برگشت داده بشه ادامه میده و شما باید کنترل کنید تا در حلقه بینهایت نیفته و البته می تونید حداکثر تکرار رو براش با MAXRECURSION تعیین کنید.

ساختار یک CTE بازگشتی:
1. فراخوانی روتین: اولین فراخوانی CTE بازگشتی یک یا چند CTE_query_definition است (کوئری که قبل از کلمه کلیدی UNION یا EXCEPT یا INTERSECT میاد) که با UNION, EXCEPT یا INTERSECT تلفیق شده. به دلیل اینکه این کوئری (ها) Result set مبنا رو برای ساختار CTE فراهم میکنه از اون به عنوان عضو Anchor (تکیه گاه) یاد میشه.
2. فراخوانی بازگشتی روتین: فراخوانی بازگشتی شامل یک یا چند CTE_query_definition است که به خود CTE ارجاع میکنه. به همین دلیل از اون به عنوان عضو بازگشتی یاد میشه.
3. آزمایش خاتمه: این چک کردن به شکل ضمنی انجام میشه و زمانی که هیچ ردیفی از آخرین اجرای روال برگشت داده نشه متوقف میشه.

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