[DB] MS-SQL 재귀계층쿼리 요약

CTE(Common Table Expression) 이라고 하는 아이가 2000년에는 없더니 2005년에 생겼다..

 

뭐하는 놈인고 하고 보니.. 흡사 임시테이블스런 놈으로 그들은 임시로 이름이 지정된 결과 집합

 

이라고도 하더라..

 

내 하고 싶었던 것은.. 조직도의 테이블에 마구마구 입력되어 있는 사원들의 정보를 사장부터

 

줄줄이 나열하여 보여주고 싶었던것..

 

그 테이블의 구조는 이리 생겼다..

 

Create Table [dbo].[Organization]

(

        Org_Cd             VarChar(10)         Not Null

,       Org_Nm             VarChar(50)         Not Null

,       Up_Org_Cd        VarChar(10)

,       Up_Org_Nm        VarChar(50)

)

 

고 치고..

 

사장 (1)

  └ 총무부 (1-1)

          └ 잠공주 (1-1-1)

          └ 깜찍이 (1-1-2)

  

  └ 부사장 (1-2)

 

뭐 이런구조로 보여주고 싶다라고 한다면..

 

2005에서 새로 제공하는 CTE를 사용하여 재귀적으로 표시를 해보자..

 

CTE의 기본구문은 BOL에 있으나.. 대충 써보면..

 

With 임시결과집합명(컬럼1, 컬럼2, 컬럼3,...n)

As

(

          기준이 되는 테이블 쿼리 (앵커멤버)

 

          Union All

 

          새끼쳐질 놈들의 쿼리

)

 

Select 컬럼1, 컬럼2, 컬럼3,...n

From   임시결과집합명

 

이런식으로 쓰게 된다..

 

그렇다면 도전해보자!!!

 

 

 

With Tree_Org(Org_Cd, Org_Nm, Org_Level)
As
(
 Select  Org_Cd, Org_Nm, 1 As Org_Level

 From   Organization
 Where  Up_Org_Cd = ''

 

 Union All

 

 Select  A.Org_Cd, A.Org_Nm, Org_Level + 1 As Org_Level
 From   Organization A
 Inner Join Tree_Org  B On A.Up_Org_Cd = B.Org_Cd
 
)

Select Org_Cd, Org_Nm, Org_Level
From  Tree_Org

 

========================

 

간단한 설명을 하면..

 

Union All 기준으로 고 위에 있는 놈은 상위부서코드가 없는.. 즉 사장인 아이를 가져오게 될것이며

 

이는 1이라는 최상위 레벨을 가지게 된다..

 

결과 :

011000                    사장                         1

 

 

그 아이를 기준으로..

 

Union All 밑에서 조직도 테이블과 임시결과집합명이라는 테이블을 조인을 걸되..

 

조직도 테이블 데이터 중에 상위조직도 코드가 사장인 애들을 가져오게 된다..

 

그렇다면 결과는..

 

 

011001                    총무부                      2

011002                    부사장                      2

 

으로 나올것이며, 임시결과집합명은 위의 결과집합을 가지게 될것이다.

 

그리고 다시 상위부서코드가, 총무부이거나 부사장인 데이터들을 가져오게 될것이고,

 

또 그 다음.. 그 다음.. 그렇게 되서..

 

011000                    사장                         1

011001                    총무부                      2

011011                    잠공주                      3

011012                    깜찍이                      3

011002                    부사장                      2

 

이런 결과를 가져오게 된다..

 

한가지 주의해야 할 점은.. 쿼리를 잘못 날렸다가는 무한한 루프님께서 왕림하실 수 있으니

 

주의해야 한다..

 

이런 왕림을 방지하기 위해..

 

 

With Tree_Org(Org_Cd, Org_Nm, Org_Level)
As
(
 Select  Org_Cd, Org_Nm, 1 As Org_Level

 From   Organization
 Where  Up_Org_Cd = ''

 

 Union All

 

 Select  A.Org_Cd, A.Org_Nm, Org_Level + 1 As Org_Level
 From   Organization A
 Inner Join Tree_Org  B On A.Up_Org_Cd = B.Org_Cd
 
)

Select Org_Cd, Org_Nm, Org_Level
From  Tree_Org
Option (MaxRecursion 3)

 

이처럼 MaxREcursion이라는 놈으로.. 재귀수준을 제한 할 수도 있다..

 

Option (MaxRecursion 3) 는 세번 헤매이다 그만 두라는 얘기랜다.. ㅡㅡ;;