[SQL] WITH절 - SUBQUERY FACTORING

-- 에러발생

 select  deptno, 부서토탈
   from  (  select  job, sum(sal)  as 직업토탈
                from   emp
                group  by  job )   as job_sumsal,
          (  select deptno, sum(sal)  as  부서토탈
               from   emp
               group  by  deptno 
               having  sum(sal)  >  ( select   avg(직업토탈)  + 3000
                                               from  job_sumsal )   )
             ) ;

위의 SQL이 실행이 안되는 이유
1. 문법 오류 : from 절 서브쿼리문의 결과인 두 테이블간의 조인 조건이 없음
2. 상관관계 오류 : job_sumsal 의 결과를 두번째 서브쿼리의 having 절에서 참조
하려 했지만 두 서브쿼리가 독립적으로 실행되어 job_sumsal 을
인식할 수 없음.

위의 문제를 해결하려면 --> with절을 사용하면 됨

with   job_sumsal  as  (   select  job, sum(sal)  as  직업토탈
                                    from    emp
                                   group  by   job  ),
        dept_sumsal  as  (   select  deptno, sum(sal)  as 부서토탈
                                    from  emp 
                                   group  by  deptno )
 select    deptno, 부서토탈
   from  dept_sumsal
   where  부서토탈 >  (  select  avg(직업토탈)  + 3000
                                  from  job_sumsal  ); 

---

문제1. 성별, 성별별 평균나이를 출력하시오

select gender, round(avg(age))
 from emp21
 group by gender;

문제2. 성별, 성별별 인원수를 출력하는데 성별별 인원수가 높은것부터 출력하시오

select gender, count(*)
 from emp21
 group by gender
 order by 2 desc;

문제3. 통신사, 통신사별 평균나이를 출력하시오

select telecom, round(avg(age))
 from emp21
 group by telecom;

문제4. 아래의 SQL이 에러가 나지 않도록 WITH 절로 변경하시오

select  telecom,  통신사평균
   from  (  select   gender, avg(age)  나이평균
               from  emp21
               group  by  gender )  as  gender_age,
            ( select  telecom, avg(age)  as  통신사평균
               from  emp21
               group  by  telecom 
               having  avg(age) > (  select  min(나이평균) + 2
                                             from  gender_age )
            )  ;

답

with gender_age as ( select gender, round(avg(age)) as 나이평균
                     from emp21
                     group by gender), 
    telecom_age as  ( select telecom, round(avg(age)) as 통신사평균 
                      from emp21
                      group by telecom )
select telecom, 통신사평균
 from telecom_age
 where 통신사평균 > (   select min(나이평균)
                        from gender_age);