EMP테이블에서부서별 최대급여자이름,급여,부서출력(상관서브쿼리,Paiewise서브쿼리,조인,인라인뷰)

EMP테이블에서부서별 최대급여자이름,급여,부서출력(상관서브쿼리,Paiewise서브쿼리,조인,인라인뷰)

1. 상관서브쿼리(un-pairwise)

select ename, sal, deptno
from emp
where sal = (  자신이속한 부서의 급여 최대값 )

select ename, sal, deptno
from emp e1
where sal = ( select max(sal) from emp e2
                  where e2.deptno = e1.deptno
                  )  --상관서브쿼리
                 
2. 서브쿼리(pairwise 방식)
select ename, sal, deptno
from emp
where (deptno, sal ) in (select deptno, max(sal) from emp
                              group by deptno)


3. 조인, 인라인뷰

select e1.ename, e1.sal, e1.deptno
from emp e1, ( select deptno, max(sal) as msal from emp group by deptno ) e2
where e1.deptno = e2.deptno
 and  e1.sal = e2.msal