11-4. 데이터 조작 언어(계층형 질의문)

■ 계층형 질의문

 

 "데이터의 서열을 결과로 시각화하는 sql "

 

예:

select level, empno, ename, mgr

 from emp

 start with ename = 'KING'

 connect by prior empno =  mgr;

level 을 출력하기 위해

start with/connect by 를 사용한다.

 

-START WITH/CONNECT BY

 

 

select level from emp

 start with ename = 'KING'

 connect by prior empno =  mgr

 

start with 컬럼명 = TREE최상위 객체의 LEVEL

connect by prior 현재level = 상위level

 

 

문제460.

start with 의 데이터를 BLAKE 로 변경해서

출력하시오

 

select level, empno, ename, mgr

 from emp

 start with ename = 'BLAKE'

 connect by prior empno =  mgr;

 

 

전체모식도

 

 

 

 

문제461.

다시 KING 부터 전체 사원을 출력하는데

BLAKE 는 제외하고 출력하시오

 

 

select level, empno, ename, mgr

 from emp

where empno not in 7698

 start with ename = 'KING'

 connect by prior empno =  mgr;

 

 

또는

select level, empno, ename, mgr

 from emp

where ename != 'BLAKE'

 start with ename = 'KING'

 connect by prior empno =  mgr;

 

 

 

문제462.

위의 결과를 다시 출력하는데

BLAKE 를 포함해서 BLAKE 의 팀원들이

다 출력되지 않게 하시오

 

select level, empno, ename, mgr

 from emp

 start with ename = 'KING'

 connect by prior empno =  mgr

 and

                      ename != 'BLAKE';

 

 

 

 

문제463.

다시 전체를 출력하는데

월급이 높은 사원부터 출력하시오

 

select level, empno, ename, mgr

 from emp

 start with ename = 'KING'

 connect by prior empno =  mgr

order by sal desc;

 

월급순서대로 출력되서

레벨순서가 깨짐.

 

 

 

문제464.

위의 결과를 다시 출력하는데

서열이 깨지지 않는 상태에서

월급이 높은 사원순으로 출력되게 하시오.

(RPAD 이용해서

서열별로 구분되게 뽑으시오)

 

select level, empno, ename, sal

 from emp

 start with ename = 'KING'

 connect by prior empno =  mgr

order siblings by sal desc;

 

 

select rpad('   ', level*2) || ename, sal

 from emp

 start with ename = 'KING'

 connect by prior empno =  mgr

order siblings by sal desc;

 

 

상사 king 에 대한 존, 스캇, 아담스

    상사 frod 에 대한 스미스

    상사 blake 에 대한 알렌,터너, 와드, 마틴

*소속을 보여 주기 위해

 띄어쓰기가 달라져서 출력된다*

 

 

 

 

문제465.

계층형 질의문과 짝꿍 함수인 sys_connect_by 함수를 이용해서 아래의 결과를 출력하시오 !

 

select ename,

 sys_connect_by_path(ename, '/') as path,

empno, mgr

 from emp

 start with ename = 'KING'

 connect by prior empno = mgr;

 

 

 

sys_connect_by_path(path_name, 'path 구분자')

TREE 구조에서 ROOT 를 찾아주는 함수이다.

PATH_NAME 부분에는 TREE 구조의 계층 이름에 해당하는 컬럼명을 적어준다.

 

최상위가 KING 이어서

SYS_CONNECT_BY_PATH 함수에서 뿌리가 된다.

그래서 모든 행에 KING 이 있는 것이다.

그리고 킹에서 부터 각각의 큰뿌리/작은뿌리들을

PATH 로 표현한다.

 

 

 

 

 

문제466.(오늘의 마지막 문제)

아래와 같이 결과를 출력하시오

 

여기서 king 앞의 / 를 없애서

출력하시오

select ename,

substr(sys_connect_by_path(ename, '/'),  2) as path

 from emp

 start with ename = 'KING'

 connect by prior empno = mgr;

 

 

다른 답

select ename, 

ltrim(sys_connect_by_path(ename,'/'),'/') as path

from emp

start with ename='KING'

connect by prior empno = mgr ;

 

 

 

 

화면 캡처: 2018-10-31 오후 4:54