cte재귀쿼리를 이용해서 이중트리구조를 짜고싶습니다.
다음과 같은 테이블(employees) 있다고 가정
id sidL sidR
| Nancy | Ina | David | |
| Ina | Ron | Dan | |
| David | James | NULL | |
| Ron | Andrew | Janet | |
| Dan | Margaret | Steven | |
| James | Michael | NULL | |
| Andrew | Robert | NULL | |
| Janet | NULL | NULL | |
| Margaret | NULL | NULL | |
| Steven | NULL | NULL | |
| Michael | NULL | NULL | |
| Robert | Laura | Ann | |
| Laura | NULL | NULL | |
| Ann | NULL | NULL | |
id값(최초 Nancy)을 기준으로 sidL 왼쪽위치 sidR 오른쪽 위치합니다.
따라서 출력은
LVL0 Nancy
LVL1 Ina ----------------------------------------------David
LVL2 Ron---------------------- Dan James------------------NULL
LVL3 Andrew------------- Janet Margaret--------- Steven Michael--------NULL NULL-------- NULL
LVL4
LVL5
.
위와같은 형식이되어야하는대요.
일단 왼쪽라인만 출력했을경우
WITH EmpCTE(id, sidL, sidR, lvl)
AS
(
-- Anchor Member(AM)
SELECT id, sidL, sidR, 0
FROM employees
WHERE id = 'Nancy'
UNION ALL
-- Recursive Memeber(RM)
SELECT E.id, E.sidL, E.sidR, M.lvl+1
FROM employees AS E
INNER JOIN EmpCTE AS M ON M.sidL = E.id
)
SELECT * FROM EmpCTE
출력값
id sidL sidR lvl
Nancy Ina David 0
Ina Ron Dan 1
Ron Andrew Janet 2
Andrew Robert NULL 3
Robert Laura Ann 4
Laura NULL NULL 5
LVL0 Nancy
LVL1 Ina ----------------------------------------------David
LVL2 Ron---------------------- Dan
LVL3 Andrew------------- Janet
LVL4 Robert--------NULL
LVL5 Laura------Ann
.
위 출력값은 왼쪽기준으로만 출력했을때 정상출력이 됩니다.
하지만 왼쪽 오른쪽 모두 출력해야하는대....
어떻게 해야하는지 고수님들의 조언 부탁드립니다.
Comment 1
-
gtm100
2013.07.26 13:39
해결되었습니다.
WITH EmpCTE(id, pid, sidL, sidR, lvl,rp)
AS
(
--Anchor Member(AM)
SELECT id, pid, sidL, sidR, 0,rp
FROM employees
WHERE id = 'Nancy'
UNION ALL
--왼쪽포지션
SELECT E.id, E.pid, E.sidL, E.sidR, M.lvl+1,E.rp
FROM employees AS E
INNER JOIN EmpCTE AS M ON M.sidL = E.id
UNION ALL
--오른쪽포지션
SELECT E.id, E.pid, E.sidL, E.sidR, M.lvl+1,E.rp
FROM employees AS E
INNER JOIN EmpCTE AS M ON M.sidR = E.id
)
SELECT id, pid, sidL, sidR, lvl,rp FROM EmpCTE
ORDER BY lvl ASC
이렇게 뽑으니 정상으로 출력되내요.
Nancy NULL Ina David 0 10000.00
Ina Nancy Ron Dan 1 2500.00
David Nancy James NULL 1 2000.00
James David Michael NULL 2 1500.00
Ron Ina Andrew Janet 2 2000.00
Dan Ina Margaret Steven 2 2000.00
Margaret Nancy NULL NULL 3 5000.00
Steven Nancy NULL NULL 3 2500.00
Andrew Nancy Robert NULL 3 5000.00
Janet Ina NULL NULL 3 5000.00
Michael Nancy NULL NULL 3 2500.00
Robert Ron Laura Ann 4 2500.00
Laura Robert NULL NULL 5 2500.00
Ann Robert NULL NULL 5 2500.00