with 절 , 계층형 쿼리 - level, start with, connect by prior

-------------------------------------with 절------------------------------------

with 절
- select문에서만 사용가능하다.
- SQL문에서 여러 번 사용되는 같은 형태의 쿼리를 with절을 사용해서 결과를 검색하고 임시로 저장한다.
- select문에서 with절의 결과를 이용할 수 있다.
- 쿼리의 실행 성능이 향상된다.

- 구성방법
            whith
            임시테이블명 as (
                        select ~
                        from ~
                        where ~
            )
            select ~
            from 임시테이블명
            where ~



예시) 부서별 총 급여를 계산했을 때 부서별 총 급여의 평균보다 총 급여를 많이 받는 부서의 이름, 총 급여조회

            --with 없이 할때
            select department_name, sum_salary
            from (select d.DEPARTMENT_NAME,  sum(salary) sum_salary  -- 부서별 평균급여 계산
                        from employees e, departments d
                        where e.DEPARTMENT_ID = d.DEPARTMENT_ID
                        group by d.DEPARTMENT_NAME) A
            where sum_salary > (select sum(sum_salary)/count(*)  -- 전체 평균
                                        from (select d.DEPARTMENT_NAME,  sum(salary) sum_salary  -- 부서별 평균급여 계산
                                                from employees e, departments d
                                                where e.DEPARTMENT_ID = d.DEPARTMENT_ID
                                                group by d.DEPARTMENT_NAME));

            -- with절을 사용해서 해보자
            with dept_salary as (
                        select d.DEPARTMENT_NAME,  sum(salary) sum_salary  -- 부서별 평균급여 계산
                        from employees e, departments d
                        where e.DEPARTMENT_ID = d.DEPARTMENT_ID
                        group by d.DEPARTMENT_NAME),
            dept_avg as(
                        select sum(sum_salary)/count(*) avg_sum_salary
                        from dept_salary)

            select *
            from dept_salary
            where sum_salary > (select avg_sum_salary
                                        from dept_avg);

 

//////////////////////////////////////////////////////계층형 쿼리
 
계층형 쿼리
            - 테이블에 저장된 데이터가 상위계층과 하위계층의 관계를 가지고 있을 때 오라클에서
               Strat with와 connect by를 이용해서 상위 -> 하위, 하위 -> 상위로 데이터를 조회할 수 있다.
            - 형식
                        select level, 컬럼명, 컬럼명, ...
                        from 테이블명
                        where 조건식
                        start with 계층의 시작점(시작행)을 지정합니다.
                        connect by 검색방향을 지정한다.
                         * connect by prior 부모행의 키 = 자식행의 키 : 부모에서 자식으로 트리 구성
                         * connect by prior 자식행의 키 = 부모행의 키 : 자식에서 부모로 트리 구성
                         * level은 계층형 쿼리에서 수행 결과의 depth를 표현하는 의사컬럼

            - 계층형 쿼리의 실행 순서
                        select level, 컬럼명, 컬럼명            ---5번                        
                        from 테이블명                            ---1번
                        where 조건식                             ---4번
                        start with 계층의 시작점               ---2번
                        connect by 검색방향                   ---3번




예시) EMPLOYEES 에서 Neena를 시작점으로 지정하고, 상위에서 하위로 검색하기
            (Neena의 부하들 찾기)

            -- Neena의 부하들 검색하기
            select level, employee_id, first_name, manager_id
            from EMPLOYEES
            start with first_name = 'Neena'  -- 검색방향 : 상위에서 하위로
            connect by prior employee_id = manager_id;  -- connect by prior 부모행의 키 = 자식행의 키

예시2) employee에서 206번 사원을 시작점으로 지정하고, 하위에서 상위로 검색하기

            select level, employee_id, first_name, manager_id
            from EMPLOYEES
            start with EMPLOYEE_id=206
            connect by prior manager_id = employee_id;

예시3)  employees 테이블의 계층 구조를 level과 lpad를 사용해서 표현하기

            select level, lpad(' ',(level*5)-5, ' ') || first_name
            from EMPLOYEES
            start with EMPLOYEE_ID = 100
            connect by prior employee_id = manager_id;

 

예시4) Neena의 부하중에 Nancy와 Nancy의 부하들을 제외하고 조회하기

            select level, lpad(' ',(level*5)-5, ' ') || first_name
            from EMPLOYEES
            --where first_name != 'Nancy'   -- 이러면 Nancy만 안나온다.
            start with first_name = 'Neena'
            connect by prior employee_id = manager_id  -- 상위 -> 하위 : prior 부모행의 키 = 자식행의 키
                                   and first_name != 'Nancy';   -- 'Nancy'와 'Nancy'의 하위 행이 모두 나오지않음