[SQL Server] CTE를 이용한 재귀쿼리 작성 및 동작방식

CTE Recursion Query, SQL Server, CTE

쿼리를 작성하지 않은지 오래되어서 정말 어렵기도 하고 버전이 올라갈수록 쿼리기능들이 향상되어서 공부하지 않으면 절대 모르겠다.

   

CTE 는 다음에 연재하도록 하고 CTE를 통한 재귀 CTE를 작성해보자.

-- CTE 의 재귀쿼리 생성하기

사용처 : 조직도 생성하기

블로그 카테고리 만들기

즉, 상위 하위 집합에 대한 연관성 생성할때 사용한다.

예제)

   

use tempdb

go

   

-- DROP TABLE Employee;

   

CREATE TABLE Employee

(Employee_id INT PRIMARY KEY,

EmployeeName VARCHAR(100),

Manager_id INT

);

GO

   

INSERT INTO Employee VALUES (1,'이원곤',NULL)

INSERT INTO Employee VALUES (5,'박주영',1)

INSERT INTO Employee VALUES (2,'권순만',1)

INSERT INTO Employee VALUES (10,'박용석',1)

INSERT INTO Employee VALUES (8,'조필호',5)

INSERT INTO Employee VALUES (9,'김석원',5)

INSERT INTO Employee VALUES (11,'이대선',2)

INSERT INTO Employee VALUES (12,'최정현',2)

INSERT INTO Employee VALUES (14,'김순현',10)

INSERT INTO Employee VALUES (15,'이문상',10)

GO

   

select * from Employee

   

   

   

;WITH DirectReports(Manager_ID, ManagerName,Employee_ID, EmployeeName ,EmployeeLevel)

AS (

--Select the root or parent records

SELECT

Manager_ID,

CAST('' AS VARCHAR(100))AS ManagerName,

Employee_ID,

EmployeeName,

0 AS EmployeeLevel

FROM Employee

WHERE Manager_ID IS NULL

   

UNION ALL

 

--Recursive part :Select the child

SELECT

e.Manager_ID,

e.EmployeeName AS ManagerName,

e.Employee_ID,

e.EmployeeName,

EmployeeLevel + 1

FROM Employee e

INNER JOIN Employee m ON e.manager_ID = m.employee_id

INNER JOIN DirectReports d ON e.Manager_ID = d.Employee_ID

)

SELECT * FROM DirectReports order by EmployeeLevel ;

   

<결과>

   

-- 동작방식의 설명하기

Anchor Member 와 Recursive Member 로 구분되며 Union All 위와 아래의 쿼리를 말하는 것이다.

1. 재귀 1회차

동작은 Anchor 쿼리가 수행되고 Recursive 쿼리와 Join 한다.

" INNER JOIN DirectReports d ON e.Manager_ID = d.Employee_ID " 조건과 일치하는 행이 없기에

Anchor 쿼리만 결과로 나온다.

SELECT

Manager_ID,

CAST('' AS VARCHAR(100))AS ManagerName,

Employee_ID,

EmployeeName,

0 AS EmployeeLevel

FROM Employee

WHERE Manager_ID IS NULL

   

-- INNER JOIN DirectReports d ON e.Manager_ID = d.Employee_ID

   

SELECT

e.Manager_ID,

e.EmployeeName AS ManagerName,

e.Employee_ID,

e.EmployeeName

 

FROM Employee e

INNER JOIN Employee m ON e.manager_ID = m.employee_id

WHERE e.Manager_id IS NULL

ORDER BY Manager_id

<결과>

   

   

2. 재귀 2 회차

위와 같이 1행이 CTE 집합테이블에 생성이 되어 있다.

그러면 아래와 같이 서로 데이터들이 JOIN문의 조건으로 수행을 하게 된다.

   

수행결과 CTE 집합테이블에는 아래와 같이 생성이 된다.

   

3. 재귀 3회차 부터는 2회차에서 수행한 Join 조건으로 무한반복 수행을 하게 된다.

반복수행이 완료되면 최종 결과집합이 CTE 테이블에 생성이 된다.

   

이러한 동작은 무한반복으로 빠질 수 있으므로 쿼리를 조심스럽게 작성해야 된다.

본 게시물은 개인적인 의견으로 작성되었으니 절대적인 정보가 아님을 알려드립니다.
참고만 하시고 궁금한 사항이 있으시면 연락주시기 바랍니다.