[Database] 16. 통계 쿼리 함수(RANK)

순위 구하기

1. rank() over()

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

2. dense_rank() over()

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

3. row_number() over()

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

-- 2. 문제 (EMP 테이블에서, 본인의 월급과 상사의 월급의 합의 순위를 내림차순으로 구하시오)

SELECT 나, 상사, 내월급, 상사월급, 월급의합,
       RANK() OVER (ORDER BY 월급의합 DESC) AS '순위'
FROM (
    SELECT e1.ename AS '나', 
           e2.ename AS '상사', 
           e1.sal AS '내월급', 
           e2.sal AS '상사월급', 
           e1.sal + IFNULL(e2.sal, 0) AS '월급의합'
    FROM emp e1 
    LEFT OUTER JOIN emp e2 ON e1.mgr = e2.empno
) nemp;