[ex25] with

ex25.with

 

    [WITH <Sub Query>]                          - with 절
    SELECT column_list                          - select 절
    FROM table_name                             - from 절
    [WHERE search_condition]                    - where 절
    [GROUP BY group_by_expression]              - group by절
    [HAVING search_condition]                   - having 절
    [ORDER BY order_expresstion [ASC|DESC]];    - order by절

 

---

with절
    - 인라인뷰(from절 서브쿼리)에 이름을 붙이는 기술
    - 단독으로는 못 씀. (select문 앞에)
    
    with 임시테이블명 as (
    결과셋 select
    )
    select 문;
    
    with절 실행 방식
    1. Materialize 방식 > 임시 테이블이 2번 이상 사용되면 내부에 임시 테이블 생성 + 반복 재사용
    2. Inline 방식 > 임시 테이블 생성없이 매번 인라인 쿼리를 반복 실행

 

select * from (select name, buseo, jikwi from tblInsa where city = '서울');

--위아래 결과 똑같음
with seoul as (select name, buseo, jikwi from tblInsa where city = '서울')
select * from seoul;

--여러개
select * 
    from (select name, age, couple from tblMen where weight < 90) a
        inner join (select name, age, couple from tblWomen where weight > 60) b
            on a.couple = b.name;

--with절 사용해서 심플하게 (with 하나로 여러개의 임시테이블)
with a as (select name, age, couple from tblMen where weight < 90), 
     b as (select name, age, couple from tblWomen where weight > 60)
select * from a inner join b on a.couple = b.name;



-- 순위함수 or rownum 
-- with절 사용하면 편리함

-- 급여 5위
-- 원래 방식
select * from (select
    name, buseo, basicpay,
    rank() over(order by basicpay desc) rnum
from tblInsa) where rnum = 5;

-- with절 사용
with insa as (
    select 
        name, buseo, basicpay,
            rank() over(order by basicpay desc) rnum
        from tblInsa
)
select * from insa where rnum = 5;