[sql] 계층형 트리구조 쿼리 [펌]

[sql] 계층형 트리구조 쿼리 [펌]

 

 

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

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

 

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

as ( 

 table 

 union

 table

)

select * from xxx order by order column

 

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

 

2014/08/12 - [DB/Oracle] - 오라클 계층형 트리구조 쿼리 (재귀호출) - START WITH .. CONNECT BY PRIOR

 

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

   |

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

   |

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

   |

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

   |   

   1depth 3

 

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

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

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

출처: https://roqkffhwk.tistory.com/140 [야근싫어하는 개발자]