[데이터 베이스]12.순위구하기 rank over 함수

-- 1. RANK
select empno, ename, sal, 1 '순위번호'
from emp
order by sal desc;

select empno, ename, sal,
rank() over (order by sal desc) '순위'
from emp;

select empno, ename, sal,
dense_rank() over (order by sal desc) '순위'
from emp;

select empno, ename, sal,
row_number() over (order by sal desc) '순위'
from emp;

-- 2. 문제 (EMP 테이블에서, 본인의 월급과 상사의 월급의 합의 순위를 내림차순으로 구하시오)
select e1.ename,
e1.sal '내월급', 
e2.sal '상사월급', 
e1.sal+ifnull(e2.sal,0) '월급의합',
dense_rank() over (order by e1.sal+ifnull(e2.sal,0) desc) '순위'
from emp e1 left outer join emp e2
on e1.mgr = e2.empno;

select 나, 상사, 내월급, 상사월급, 
rank() over (order by 상사월급 desc) '순위'
from
(
select e1.ename '나', e2.ename '상사', e1.sal '내월급', e2.sal '상사월급', 
e1.sal+ifnull(e2.sal, 0) '월급의합'
from emp e1 left outer join emp e2 on e1.mgr = e2.empno
) nemp;