[MSSQL] 계층형 트리구조 쿼리

MS-SQL 2005 부터 추가된 재귀쿼리를 설명 하고자 한다.

트리구조 쿼리에 사용하면 유용할 거 같아서 포스팅을 하게 되었다.

with xxx(col 1,col 2...col n)

as ( 

 table 

 union

 table

)

select * from xxx order by order column

오라클 재귀쿼리 호출했던 같은 구조의 테이블로 샘플을 짜보겠다.

 

 

1. 테이블 생성

create table tree_table(
	id int not null,
    parent_id int not null,
    name nvarchar(30) not null
);

2. 트리쿼리 출력을 위한 임의의 데이터 생성

-- 1depth 
insert into tree_table(id,parent_id,name)
values(1,0,'1depth 1');
insert into tree_table(id,parent_id,name)
values(2,0,'1depth 2');
insert into tree_table(id,parent_id,name)
values(3,0,'1depth 3');

-- 2depth 1
insert into tree_table(id,parent_id,name)
values(4,1,'2depth 1-1');
insert into tree_table(id,parent_id,name)
values(5,1,'2depth 1-2');
insert into tree_table(id,parent_id,name)
values(6,1,'2depth 1-3');

-- 2depth 2
insert into tree_table(id,parent_id,name)
values(7,2,'2depth 2-1');
insert into tree_table(id,parent_id,name)
values(8,2,'2depth 2-2');
insert into tree_table(id,parent_id,name)
values(9,2,'2depth 2-3');

-- 3depth 1
insert into tree_table(id,parent_id,name)
values(10,4,'3depth 1-1');
insert into tree_table(id,parent_id,name)
values(11,4,'3depth 1-2');
insert into tree_table(id,parent_id,name)
values(12,4,'3depth 1-3');

3. 재귀쿼리로 트리목록 출력

WITH tree_query  AS (
  SELECT  
         id
       , parent_id
       , name
       , convert(varchar(255), id) sort  
  	   , convert(varchar(255), name) depth_fullname
    FROM tree_table
    WHERE parent_id = 0
    UNION ALL 
    SELECT
          B.id
        , B.parent_id
        , B.name
        , convert(varchar(255), convert(nvarchar,C.sort) + ' > ' +  convert(varchar(255), B.id)) sort
        , convert(varchar(255), convert(nvarchar,C.depth_fullname) + ' > ' +  convert(varchar(255), B.name)) depth_fullname
    FROM  tree_table B, tree_query C
    WHERE B.parent_id = C.id
) 
SELECT id, parent_id, name, depth_fullname FROM tree_query order by SORT

위의 재귀쿼리 실행결과를 확인해 보도록 하자 

위와같이 트리구조로 출력되는것을 확인하였다. 

구조로 따지자면,

   1depth 1

   |

   -------- 2depth 1-1

   |                 |

   |                 ---- 3depth 1-1

   |                 |

   |                 ---- 3depth 1-2

   |                 |

   |                 ---- 3depth 1-3

   -------- 2depth 1-2

   |

   -------- 2depth 1-3

   |

   1depth 2

   |

 

출처 : http://roqkffhwk.tistory.com/140

   -------- 2depth 2-1

   |

   -------- 2depth 2-2

   |

   -------- 2depth 2-2

   |   

   1depth 3

와같은 구조로 출력되었다.

정렬이 중요한대 위의 정렬은 데이터가 많지 않아 잘 나오는거같다.

만약 재귀쿼리가 정상적으로 출력이 되지 않으면 sort에 대한 부분은 고민하고 수정해보도록 하자.

마지막으로 잘 활용해서 실무에 적용해보도록 합시다~