[MSSQL] 재귀 쿼리, 트리 구조 (CTE 재귀)

 

 

 

 

 

 

재귀 쿼리

CTE를 이용하여 재귀 쿼리를 작성해보자

 

WITH TEMP AS (
	SELECT 컬럼 FROM 테이블명
	UNION ALL
	SELECT 컬럼 FROM 테이블명
	INNER JOIN TEMP ON 테이블명.컬럼 = TEMP.컬럼
)

 

 

 

예제 1

 

재귀 쿼리를 통해 1부터 2씩 증가된 숫자를 출력 (200 이하)

 

;WITH TA AS
(
	SELECT 1 NUM
    
    UNION ALL
    
    SELECT NUM + 2
    FROM TA
    WHERE NUM + 2 <= 200
)
SELECT * FROM TA

 

 

결과

NUM

1

3

5

...

197

199

 

 

 

 

 

예제 2

보통 트리 구조의 테이블은 칼럼 정보에 자신의 정보 값과 부모 정보의 값을 가지고 있다.

간단히 말해 본인의 정보와 더불어 부모의 값 (ID, CODE...)을 저장한다.

 

ID	NAME	PID
1	회장	NULL
2	편집장	1
3	마케팅팀장	1
4	꽁쥐 대리	2

 

위의 트리 구조를 살펴보면,

회장(1)을 최상위 노드로 편집장(2)과 마케팅팀장(3)이 자식 노드로 존재한다.

그리고 편집장(2) 아래로 꽁쥐 대리(4)가 존재한다.

 

 

자 그럼 꽁쥐 대리를 기준으로 상위 노드값을 조회해보자

 

ID	NAME	PID
2	편집장	1
4	꽁쥐 대리	2

 

꽁쥐 대리는 PID가 2라는 정보를 가짐으로써, ID값이 2인 행이 부모라는 정보를 담고 있다.

 

그 결과로 나온 편집장은 역시 PID가 1이라는 정보를 가짐으로써, ID 값이 1인 행이 부모라는 정보를 담고 있다.

 

ID	NAME	PID
1	회장	NULL
2	편집장	1
4	꽁쥐 대리	2

 

즉, 이렇게 꽁쥐 대리 위로는 총 2단계의 부모가 존재하는 것을 알 수 있다.

 

 

 

 

자 이제 하고자 하는 바를 알았으니 이를 쿼리로 작성해보자

꽁쥐 대리를 기준으로 상위 단계의 부모를 모두 출력해보자

 

WITH TEMP AS (
  -- anchor
  SELECT ID, NAME, PID
  FROM TREE_TABLE
  WHERE NAME = '손꽁쥐 대리'
    
  UNION ALL
    
  -- recursive
  SELECT B.ID, B.NAME, B.PID
  FROM TEMP A
  INNER JOIN TREE_TABLE B ON A.PID = B.ID
)
SELECT *
FROM TEMP
ORDER BY ID
OPTION(MAXRECURSION 5)

 

결과 데이터

ID	NAME	PID
1	회장	NULL
2	편집장	1
4	꽁쥐 대리	2

 

 

 

옵션

OPTION(MAXRECURSION 값)

 

재귀 횟수를 제한한다.

0부터 32,767 사이의 값을 사용할 수 있다.

0으로 할 경우 제한이 적용되지 않는다.

무한루프 방지를 위해 적당한 값을 넣는 게 좋겠다.