* SQL 계층형 질의문 connect by 절 level 서열을 주어 데이터 출력

• 데이터에서 서열을 출력하는 SQL 문법

ex) emp table 에는 서열이 존재한다.
→숨어있는 서열을 화면에 표시하는것.
→서열과 관련된 컬럼은 mgr (관리자 번호 이다.)
→ 관리자번호(mgr)를 이용한 서열 출력 할 수 있다. 

connect by 절을 쓸때 절들의 역할 
where : 데이터들을 모두 가져온 뒤 마지막으로 조건절에 맞게 정리한다. 
start with : 계층구조를 만들때 기준이 되는 데이터를 입력한다. 
connect by prior : 각 행들의 연관된 관계를 적어준다. 

* 숫자를 1부터 10 까지 출력 하는 방법

select level as 숫자
from dual
connect by level <= 10;

→ connect by 절에 기술한 숫자만큼 1~ n 개 출력된다.

예제 king 을 기준으로 계층을 출력하시오 ( 이름과, 월급, 직업, 관리자번호를 출력 ) 

select ename, job, sal, mgr , level 
from emp
start with ename = 'KING'
connect by prior ename = mgr

466 clark 을 시작점으로 두고 서열순서를 출력, 사원번호,사원이름, 서열, 직업, 월급을 출력

select ename, empno, job, sal, level 
from emp
start with ename = 'CLARK'
connect by prior empno = mgr ;

467 king 을 시작점으로 두고 서열순서로 사원테이블 컬럼 출력, 이름,서열,직업,월급 출력 하면서 월급이 2400 이상인 사원들 출력

select ename , level, job, sal
from emp
where sal >2400 
start with ename = 'KING'
connect by prior empno = mgr ;

-> 조건절만 추가해 주면 된다. 

 level 만큼 앞에 공백을 채워넣어 이름출력

select rpad(' ',level*2)||ename as employee , level, job, sal
from emp
start with ename = 'KING'
connect by prior empno = mgr;

 

공백에 level * 2 만큼 채워져서 나오는 것
rpad ?
처음에 배울 땐  rpad (sal, 10, '*') 로 월급 출력
-> 전체 10 자리 잡고 월급을 출력하고 나머지 공간에 * 을 채워 넣어라 라고 배웠다.

지금은
rpad (' ', 12) → 이 뜻은 공백(' ') 을 12 개 채워넣어라 라는 의미이다.

---

문제 469  blake 를 제외하고 데이터를 출력하여라 (계층형 질의문 이용하여) 

select rpad(' ',level*3)||ename as employee , level, job, sal 
from emp 
where ename != 'BLAKE'
start with ename = 'KING'
connect by prior empno = mgr;

 

계층형 질의문 결과에서 특정행만 제외하려면 where 절에 제외시킬 검색조건을 적으면 된다.

---

데이터를 선택하여 계층순서로 결과를 출력할때 분기를 제거하는 방법
blake 뿐 아니라 팀원까지 제외하는법

select rpad(' ',level*3)||ename as employee , level, job, sal 
from emp 
start with ename = 'KING' 
connect by prior empno = mgr
     and ename != 'BLAKE';

 

팀장 뿐만 아니라 팀원들까지 출력에서 제외시키고 싶다면
→ where 절이 아닌 connect by 절에 ane ename ≠ 'blake' (팀장이름)적는다.

만약 BLAEK , CLARK 두팀 모두를 제외 시켜 출력하려면 

select rpad(' ', level*3)||ename as EMPLOYEE , level, job ,sal
from emp 
start with ename = 'KING'
connect by prior empno = mgr 
     and ename NOT IN( 'BLAKE', 'CLARK');

 

심화 : 계층형질의문으로 서열을 주고  서열안에서 정렬하여 데이터 출력하기

- order siblings by 를 써준다.

계층형 질의문의 결과를 유지하며 데이터를 정렬하여 데이터를 출력한다.
-> 즉 계층으로 뽑은 결과안에서 데이터를 정렬 하는 방법 이다. 

문제 474  계층형으로 뽑은 결과물에 각각 월급이 높은 순으로 데이터를 출력 

select rpad(' ', level*3)||ename, level, job, sal 
from emp
start with ename = 'KING' 
connect by prior empno = mgr 
order siblings by sal desc;

문제 475 계층형으로 뽑은 결과물에서 입사일이 늦은 순으로 데이터를 출력 

select rpad(' ',level*3)||ename, level, hiredate
from emp 
start with ename = 'KING'
connect by prior empno = mgr
order siblings by hiredate desc;

 

서열 순서를 가로로 출력하는 방법

- sys_connect_by_path

select ename, sys_connect_by_path(ename,'/') as path
from emp 
stART with ename = 'KING' 
connect by prior empno = mgr ;

 

→ 서열순으로 이름을 가로로출력하고 있다.
→ 이름과 이름 사이는 / 로 연결 되어있다. ',' 콤마로도 바꿀 수 있음 ( 내가 원하는 방법 )

해석 : 'SMITH' 는 'KING', 'JONES', 'FORD' 밑에 있다.