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