계층형 트리구조 쿼리를 이용하여 트리구조 출력

계층형 트리구조 쿼리를 이용하여 트리구조 출력

START WITH .. CONNECT BY PRIOR 구문을 이용하여 트리구조를 심플하게 출력할 수 있다.

데이터 예를 들도록 해보자

1. 테이블 생성

create table tree_table(
	id number not null,
    parent_id number not null,
    name nvarchar2(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. 재귀쿼리로 트리목록 출력

SELECT id,parent_id,name, LTRIM (SYS_CONNECT_BY_PATH (name, ' > '), ' > ') AS depth_fullname
 FROM tree_table 
 START WITH parent_id = 0
 CONNECT BY PRIOR id=parent_id

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

※ ORACLE 11g 에서 ms-sql 2005에서 제공되는 재귀쿼리가 동일하게 동작된다. 

    다음과 같이 쿼리문을 주어서 실행 하더라도 같은 결과가 출력된다.

WITH tree_query(id,parent_id,name,sort, depth_fullname)  AS 
(
  SELECT  
         id
       , parent_id
       , name
       , ''||id as sort
       , ''||name as depth_fullname
    FROM tree_table
    WHERE parent_id = 0
    UNION ALL 
    SELECT
          B.id
        , B.parent_id
        , B.name
        , C.sort || ' > ' || B.id as sort
        , C.depth_fullname || ' > ' || B.name as  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

위와같은 구조로 출력된 셈이다.

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