부서별 평균 월급보다 높은 월급의 사원 목록 구하기

질문

※ emp 테이블 정보가 당장 없어 union all 을 이용해서 가상의 emp 테이블 생성했습니다.

with emp as (
select 'Jane' ename, 1 as deptno, 100 as sal from dual union all 
select 'Shawn' ename, 1 as deptno, 200 as sal from dual union all
select 'Ted' ename, 1 as deptno, 250 as sal from dual union all
select 'steve' ename, 2 as deptno, 700 as sal from dual union all
select 'John' ename, 2 as deptno, 600 as sal from dual
)
select ename,deptno,sal 
from emp e
where sal > (select avg(sal) from emp)

이렇게 풀었는데 부서별로라는 말이 걸린다고 합니다.
(출처 :  https://cafe.naver.com/sqlpd/37131) 

 

답변

 

질문자의 답은 전체 평균보다 높은 급여의 사원목록이 결과로 나옵니다.
그래서 추가적인 조건을 주어야 합니다.

with emp as (
select 'Jane' ename, 1 as deptno, 100 as sal from dual union all 
select 'Shawn' ename, 1 as deptno, 200 as sal from dual union all
select 'Ted' ename, 1 as deptno, 250 as sal from dual union all
select 'steve' ename, 2 as deptno, 700 as sal from dual union all
select 'John' ename, 2 as deptno, 600 as sal from dual
)
select ename,deptno,sal 
from emp e
where sal > (select avg(sal) from emp where deptno = e.deptno )

 

 

추가설명

 

쉽게 해결하려면 역시 window 함수입니다.

 

with emp as (
select 'Jane' ename, 1 as deptno, 100 as sal from dual union all 
select 'Shawn' ename, 1 as deptno, 200 as sal from dual union all
select 'Ted' ename, 1 as deptno, 250 as sal from dual union all
select 'steve' ename, 2 as deptno, 700 as sal from dual union all
select 'John' ename, 2 as deptno, 600 as sal from dual
)
select 
ename,deptno,sal
,avg(sal) over (partition by deptno) as dept_sal -- 부서별 평균 확인
from emp x

부서별 평균을 확인했으니 결과를 필터링 하면 됩니다.

 

with emp as (
select 'Jane' ename, 1 as deptno, 100 as sal from dual union all 
select 'Shawn' ename, 1 as deptno, 200 as sal from dual union all
select 'Ted' ename, 1 as deptno, 250 as sal from dual union all
select 'steve' ename, 2 as deptno, 700 as sal from dual union all
select 'John' ename, 2 as deptno, 600 as sal from dual
),
step1 as (
select ename,deptno,sal,avg(sal) over (partition by deptno) as dept_sal 
from emp x
) select * from step1 where sal > dept_sal