MSSQL CTE 재귀, 트리구조 쿼리구현

http://blog.jhfrk.net/339

재귀적 CTE 구조 (SQL Server 2005 온라인 설명서)

의사 코드 및 의미 체계

재귀적 CTE 구조에는 앵커 멤버와 재귀 멤버가 적어도 하나씩 포함되어야 합니다. 다음 의사 코드에서는 단일 앵커 멤버와 단일 재귀 멤버가 포함된 간단한 재귀적 CTE의 구성 요소를 보여 줍니다.

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

재귀 실행의 의미 체계는 다음과 같습니다.

1. CTE 식을 앵커 멤버와 재귀 멤버로 분할합니다.
   
   
2. 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T₀) 을 만듭니다.
   
   
3. T_i는 입력으로 사용하고 T_i+1은 출력으로 사용하여 재귀 멤버를 실행합니다.
   
   
4. 빈 집합이 반환될 때까지 3단계를 반복합니다.
   
   
5. 결과 집합을 반환합니다. 이것은 T₀에서 T_n까지의 UNION ALL입니다.

--------------------------------------------------------------------------------------------

예)

create table test_rcte
(
 idx int,
 p_idx int,
 val varchar(10)
);

insert into test_rcte(idx, p_idx, val) values (1, null, 'root');
insert into test_rcte(idx, p_idx, val) values (2, 1, 'user1');
insert into test_rcte(idx, p_idx, val) values (3, 1, 'user2');
insert into test_rcte(idx, p_idx, val) values (4, 1, 'user3');
insert into test_rcte(idx, p_idx, val) values (5, 2, 'user4');
insert into test_rcte(idx, p_idx, val) values (6, 2, 'user5');
insert into test_rcte(idx, p_idx, val) values (7, 3, 'user6');
insert into test_rcte(idx, p_idx, val) values (8, 3, 'user7');
insert into test_rcte(idx, p_idx, val) values (9, 4, 'user8');
insert into test_rcte(idx, p_idx, val) values (10, 5, 'user9');
insert into test_rcte(idx, p_idx, val) values (11, 5, 'user10');
insert into test_rcte(idx, p_idx, val) values (12, 6, 'user11');
insert into test_rcte(idx, p_idx, val) values (13, 6, 'user12');
insert into test_rcte(idx, p_idx, val) values (14, 7, 'user14');
insert into test_rcte(idx, p_idx, val) values (15, 7, 'user15');
insert into test_rcte(idx, p_idx, val) values (16, 8, 'user16');
insert into test_rcte(idx, p_idx, val) values (17, 8, 'user17');
insert into test_rcte(idx, p_idx, val) values (18, 10, 'user18');

MSSQL	val	lvl		ORACLE	val	lvl
	root	1			root	1
	user1	2			user1	2
with cte (val, p_idx, idx, lvl)	user2	2		select val, level as lvl	user3	2
as	user3	2		from test_rcte	user2	2
(	user8	3		start with p_idx is null	user6	3
select val, p_idx, idx, 1 as lvl	user6	3		connect by prior idx = p_idx	user4	3
from test_rcte	user7	3		order by level;	user7	3
where p_idx is null	user4	3			user8	3
union all	user5	3			user5	3
select a.val, a.p_idx, a.idx, lvl + 1	user11	4			user17	4
from test_rcte a, cte b	user12	4			user16	4
where a.p_idx = b.idx	user9	4			user14	4
)	user10	4			user15	4
select val, lvl from cte	user16	4			user12	4
order by lvl	user17	4			user11	4
	user14	4			user9	4
	user15	4			user10	4
	user18	5			user18	5

MSSQL	val	lvl	path
	root	1	/ root
with cte (val, p_idx, idx, lvl, path)	user1	2	/ root / user1
as	user2	2	/ root / user2
(	user3	2	/ root / user3
select val, p_idx, idx, 1 as lvl, convert(varchar(100), ' / ' + val) path	user8	3	/ root / user3 / user8
from test_rcte	user6	3	/ root / user2 / user6
where p_idx is null	user7	3	/ root / user2 / user7
union all	user4	3	/ root / user1 / user4
select a.val, a.p_idx, a.idx, lvl + 1, convert(varchar(100), path + ' / ' + a.val)	user5	3	/ root / user1 / user5
from test_rcte a, cte b	user11	4	/ root / user1 / user5 / user11
where a.p_idx = b.idx	user12	4	/ root / user1 / user5 / user12
)	user9	4	/ root / user1 / user4 / user9
select val, lvl, path from cte	user10	4	/ root / user1 / user4 / user10
order by lvl	user16	4	/ root / user2 / user7 / user16
	user17	4	/ root / user2 / user7 / user17
	user14	4	/ root / user2 / user6 / user14
	user15	4	/ root / user2 / user6 / user15
	user18	5	/ root / user1 / user4 / user9 / user18
ORACLE	val	lvl	path
	root	1	/ root
select val, level as lvl, sys_connect_by_path(val,' / ') path	user1	2	/ root / user1
from test_rcte	user3	2	/ root / user3
start with p_idx is null	user2	2	/ root / user2
connect by prior idx = p_idx	user6	3	/ root / user2 / user6
order by level;	user4	3	/ root / user1 / user4
	user7	3	/ root / user2 / user7
	user8	3	/ root / user3 / user8
	user5	3	/ root / user1 / user5
	user17	4	/ root / user2 / user7 / user17
	user16	4	/ root / user2 / user7 / user16
	user14	4	/ root / user2 / user6 / user14
	user15	4	/ root / user2 / user6 / user15
	user12	4	/ root / user1 / user5 / user12
	user11	4	/ root / user1 / user5 / user11
	user9	4	/ root / user1 / user4 / user9
	user10	4	/ root / user1 / user4 / user10
	user18	5	/ root / user1 / user4

출처 :  http://blog.jhfrk.net/339

여기서 재귀 호출이 100번 이상일 때 자동적으로 에러 메세지를 던진다.
이 때, 맨 아래에 OPTION (MAXRECURSION 0) 를 추가해주면 무한 반복한다.
0이 아닌 다른 숫자를 했을 때는 해당 횟수만큼 재귀호출을 한다.

이로써, 트리구조는 해결되었다 -_-;
오랜 숙변이 내려가는거 같네.