[SQL 쿡북] 13. 계층적 쿼리

13.1 상위-하위 관계 표현하기

<DB2, Oracle, PostgreSQL>
 select a.ename || ' works for ' || b.ename as emps_and_mgrs
   from emp a, emp b
  where a.mgr = b.empno

< MySQL>
 select concat(a.ename, ' works for ',b.ename) as emps_and_mgrs
   from emp a, emp b
  where a.mgr = b.empno

<SQL Server>
 select a.ename + ' works for ' + b.ename as emps_and_mgrs
   from emp a, emp b
  where a.mgr = b.empno
--------------------------------------------------------------------
select a.empno, b.empno
  from emp a, emp b
--------------------------------------------------------------------
 select a.empno, b.empno mgr
   from emp a, emp b
  where a.mgr = b.empno
--------------------------------------------------------------------
select a.ename,
       (select b.ename
          from emp b
         where b.empno = a.mgr) as mgr
  from emp a
--------------------------------------------------------------------
/* ANSI */
select a.ename, b.ename mgr
  from emp a left join emp b
    on (a.mgr = b.empno)
--------------------------------------------------------------------
/* Oracle */
select a.ename, b.ename mgr
  from emp a, emp b
 where a.mgr = b.empno (+)

셀프 조인에서는 EMPNO와 MGR 간에 동등 조인을 사용하여 MGR에 대해 null을 가진 사원을 필터링합니다. 

 

13.2 자식-부모-조부모 관계 표현하기

13.3 테이블의 계층 뷰 생성하기

13.4 지정한 상위 행에 대한 모든 하위 행 찾기

<DB2, PostgreSQL, SQL Server>
   with x (ename,empno)
      as (
  select ename,empno
    from emp
   where ename = 'JONES'
   union all
  select e.ename, e.empno
    from emp e, x
   where x.empno = e.mgr
 )
 select ename
   from x

<Oracle>
 select ename
   from emp
  start with ename = 'JONES'
 connect by prior empno = mgr

13.5 리프, 분기, 루트 노드 행 확인하기

CONNECT BY 를 사용하는 Oracle 사용자와 WITH를 사용하는 DB2/SQL Server 사용자의 경우, 트리 계층구조를 사용하기 쉽고 재귀 계층 구조보다 더 효율적일 수 있습니다. 사용 주의.

 

<DB2, PostgreSQL, MySQL, SQL Server>
  select e.ename,
         (select sign(count(*)) from emp d
           where 0 =
             (select count(*) from emp f
               where f.mgr = e.empno)) as is_leaf,
         (select sign(count(*)) from emp d
           where d.mgr = e.empno
            and e.mgr is not null) as is_branch,
         (select sign(count(*)) from emp d
          where d.empno = e.empno
            and d.mgr is null) as is_root
   from emp e
 order by 4 desc,3 desc

<Oracle>
   select ename,
          connect_by_isleaf is_leaf,
          (select count(*) from emp e
            where e.mgr = emp.empno
              and emp.mgr is not null
              and rownum = 1) is_branch,
          decode(ename,connect_by_root(ename),1,0) is_root
     from emp
    start with mgr is null
 connect by prior empno = mgr
 order by 4 desc, 3 desc

1. 사원이 리프 노드인지 확인한다. 관리자가 아니면 리프노드

2. 분기 노드 찾기 사원이 관리자이고 다른 사람을 위해 일하는 경우

3.  루트 노드 찾기. 여러 루트 노드가 있을 수 있다면, SIGN을 사용하거나 IS_BRANCH 및 IS_LEAF에 대해 이전에 본 것처럼 스칼라 서브쿼리에서 1행 테이블을 사용할 수 있음

 

Oracle Database 10g 이전 버전의 Oracle 사용자는 다른 RDBMS에 대한 해법을 따를 수 있다. 위의 예시는 10g이상 사용하는 경우 CONNECT_BY_ROOT 및 CONNECT_BY_ISLEAF 의 두 가지 함수를 활용할 수 있습니다.