[MS-SQL] SQL(쿼리) CTE 이용하여 재귀적(트리) 표현하기 > db

개발질 하다가 트리 구조를 가져와야 하는 녀석이 있어서 , 오라클 처럼 재귀표현을 할 수 있는 쿼리가 있나 찾아보니 있네요.

2005년 버전 이후부터 된다고 합니다.

 

겸사 겸사... 제가 까먹음..나중에 써먹고.. 필요한분 쓰시라고 올려둡니다.

 

먼저 테이블은 아래와 같습니다.

 

 

이 녀석을  아래와 같은 트리 구조로 뽑아 보겠습니다.

 

 

이런 형태로 트리로 뽑을 수가 있습니다.

 

해당하는 쿼리는 아래와 같습니다.

 

WITH test(parent , node , level , sort)
AS
(
 SELECT parent , node , 1 AS level ,
           CONVERT(varchar(255), parent + ' ' + node) as sort
    FROM   ctest
    WHERE  parent = '1'
 UNION ALL
    SELECT a.parent , a.node , level+1 AS level ,
           CONVERT(varchar(255), RTRIM(sort) + '|    ' + a.parent + ' ' + a.node) as sort
 FROM   ctest a , test b
 WHERE  a.parent = b.node
)
SELECT * FROM test
order by sort

일단 두번째 사진처럼 뽑기 위한 쿼리는 위와 같습니다.

 

구조를 잠시 설명 드리면 ,

with (

 select ....

union all

 select ....

)

select....

 

처음 select 에 해당하는 부분이 실제 데이터를 가져오게한 쿼리의

SELECT parent , node , 1 AS level ,
           CONVERT(varchar(255), parent + ' ' + node) as sort
    FROM   ctest
    WHERE  parent = '1'
이 부분인데 , 여기서 WHERE  parent = '1' 이런형태로 루트가 되는 녀석을 지정해주면 됩니다.

 

두번째 select 에 해당하는 부분이 실제 데이터를 가져오게한 쿼리의

 SELECT a.parent , a.node , level+1 AS level ,
           CONVERT(varchar(255), RTRIM(sort) + '|    ' + a.parent + ' ' + a.node) as sort
 FROM   ctest a , test b
 WHERE  a.parent = b.node
이 부분인데 , 여기서 WHERE  a.parent = b.node 이 부분의 모자 관계입니다.

 

머... 아웃풋 내기에 정신이 없어 , 상세 스펙이 나온 MSDN을 제대로 보지는 않았지만 , 왠지 생긴거만 봐서는

재귀로 돌면서 두번째 select에 설정된 부모기준으로 첫번째 select 로 가져온 녀석을 합치는거 아닌가 하는 생각이 듭니다.

정말 그런건지는 상세 스펙을 보지 않아서 모르고 , 생긴거만 봐서는 그렇지 않은가 합니다.

 

아~ 그리고 마지막 select 는 무엇을 보이게 할지 설정하는 부분이라고 보시면 되구요.

 

level << 이 부분은 몇 레벨인지 표시하기 위한 부분입니다.

 

sort 이 부분은 계층적으로 표현하기 위한 , 일종의 후루꾸 기법이 아닌가 하는 생각이 듭니다.

실제로 sort 컬럼을 지워버리시고 , 데이터를 추출하면 계측정으로 데이터는 가져오지만 계층적으로 표현은 안됩니다.

예를 들어....

0레벨

1레벨

1레벨

2레벨

2레벨

3레벨

3레벨

이딴식으로 나오드라구요.

 

아마도 대부분의 개발자분들이

0레벨

  1레벨

    2레벨

       3레벨

       3레벨

    2레벨

  1레벨

표현이 필요하지 않을까 합니다. 모~ 저만 필요한건지는 모르겠지만요.

 

그럼~ 대한민국 모든 개발자가 칼퇴하여 , 지랄같은 직업이라고 접는 일이 없는 그날까지.....