재귀호출 Using Common Table Expressions

재귀호출(Recursive Queries)이라고 하지요.

인사구조, 조직 등의 피라미드구조 나 BOM( 소요자재내역 ) 을 분석할때 필요한 알고리즘입니다.

데이타 구조상으로 보면 부모 - 자식 관계의 레코드가 원레벨로 연결되어 있습니다. 모자 관계라고도 합니다.

SQL서버 에서 알고리즘을 지원하지 않으면  WHILE 문이 FETCH 문으로 전개하는 프로시져을 작성해야 합니다.

최상위에 있는 레코드를 0 레벨로 보고 그 하위에 있는 레코드는 1레벨로 정하는 겁니다.

레벨을 증가시켜서 반복적으로 구조를 헤치고 들어가도록 루핑을 돌려면 되지요.

SQL Server 2008에서 지원하는 것 같은데 CTE( Common Table Expressions ) 을 사용해서 

재귀호출이 기능을 작성할수 있습니다.

재귀호출 뼈대

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

AS

(

CTE_query_definition -- Anchor member is defined.

UNION ALL

CTE_query_definition -- Recursive member is defined referencing cte_name.

)

-- Statement using the CTE

SELECT *

FROM cte_name

Example

-- 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'Sanchez', 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);

-- execution

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)

AS (-- Anchor member definition

SELECT e.ManagerID, e.EmployeeID, e.Title, e.Deptid, 

0 AS Level

FROM dbo.MyEmployees e

WHERE e.ManagerID IS NULL

UNION ALL

-- Recursive member definition

SELECT e.ManagerID, e.EmployeeID, e.Title, e.Deptid,

Level + 1

FROM dbo.MyEmployees AS e

INNER JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID)

-- Statement that executes the CTE

SELECT ManagerID, EmployeeID, Title, Level

FROM DirectReports

OPTION (MAXRECURSION 1000) -- Default 가 100이므로 지정하지 않았을경우 에러가 발생

또다른 예제

출처) http://sqlwithmanoj.wordpress.com/2011/12/23/recursive-cte-maximum-recursion-100-has-been-exhausted/

DECLARE
	@startDate DATETIME,
	@endDate DATETIME

SET @startDate = '11/10/2011'
SET @endDate = '03/25/2012'

; WITH CTE AS (
	SELECT
		YEAR(@startDate) AS 'yr',
		MONTH(@startDate) AS 'mm',
		DATENAME(mm, @startDate) AS 'mon',
		DATEPART(d,@startDate) AS 'dd',
		@startDate 'new_date'
	UNION ALL
	SELECT
		YEAR(new_date) AS 'yr',
		MONTH(new_date) AS 'mm',
		DATENAME(mm, new_date) AS 'mon',
		DATEPART(d,@startDate) AS 'dd',
		DATEADD(d,1,new_date) 'new_date'
	FROM CTE
	WHERE new_date < @endDate
	)
SELECT yr AS 'Year', mon AS 'Month', count(dd) AS 'Days'
FROM CTE
GROUP BY mon, yr, mm
ORDER BY yr, mm
OPTION (MAXRECURSION 1000)

Output:-

Year	Month		Days
2011	November	22
2011	December	31
2012	January		31
2012	February	29
2012	March		24