かけがえのない愛

MS-SQL 2005에서는 재귀적 관계를 가지는 데이터를 출력할 수 쿼리를 지원한다.
(MS-SQL2000 지원안함)

Oracle 에서의 Connect By와 같은 역할을 하는것으로
트리 구조의 메뉴를 출력할 때 사용한다.

예를 들어 어느 회사 조직도가 아래와 같다 하자.

부장 홍길동        차장 김길동               과장 고길동                      대리 서길동                      대리 안길동               과장 이길동                      대리 안길동                      사원 최길동        차장 채길동 ....

부장 홍길동을 필두로 하여 트리 구조로 만들어져 있는것을 볼 수 있다.

위 트리 구조를 데이터로 간단하게 표현하면 아래와 같다.

사원번호       직함        성명       소속사번001              부장       홍길동      null 002              차장       김길동      001 003              과장       고길동      002 004              대리       서길동      003 005              대리       안길동      003 006              과장       이길동      002 007              대리       안길동      006 008              사원       최길동      006 009              차장       채길동      001

고길동의 직장 상사는 002 번인 차장 김길동 이다 라는 식으로 찾을 수 있겠다.
그럼 이 트리 구조를 쿼리로 작성할 때 차장 김길동을 포함한 부하직원을 모두 찾는다고 가정하자.
방법으로는 커서를 이용하여 찾는 방법도 있을 것이고, 몇개의 컬럼을 더 포함해서 찾을 수도 있을 것이다.

그러나 CTE(Common Table Expression) 기능중 WITH를 이용하면 쉽게 쿼리가 가능하다.
쿼리는 아래와 같다.

쿼리문
WITH EMP (사원번호, 직함, 성명, 소속사번) AS
(
     SELECT 사원번호, 직함, 성명, 소속사번
     FROM    사원
     WHERE  사원번호 = ''002'' -- 김길동
     UNION ALL
     SELECT  T1.사원번호, T1.직함, T1.성명, T1.소속사번
     FROM    사원 T1 INNER JOIN EMP T2 ON T2.사원번호 = T1.소속사번
)
SELECT *
FROM EMP;

실행 결과
002              차장       김길동      001
003              과장       고길동      002
004              대리       서길동      003
005              대리       안길동      003
006              과장       이길동      002
007              대리       안길동      006
008              사원       최길동      006