순위함수

SELECT 절
    SELECT 컬럼, 함수, 그룹함수, SUB QUERY, OVER() PARTITION BY
    FROM 테이블, SUB QUERY
    [ WHERE ] 조건 IN AND ALL ANY LIKE < > <= != = SUB QUERY
    [ GROUP BY ] 컬럼
    [ HAVING ] 그룹핑의 조건
    [ ORDER BY ] 컬럼 ASC, DESC
    [ START BY ] 계층형
    [ CONNECT BY ] 연결형 PRIOR(상햐으 하향)

JOIN 
    INNER,
    FULL OUTER
    CROSS
    OUTER (RIGHT, LEFT)
    SELF 

 

 

 

 

   순위함수
    RANK()          1 2 3 3 5 6
    DANCE_RANK()    1 2 3 3 5 6
    ROW_NUMBER()    1 2 3 4 5 6
    ROWNUM      --> 123456 순서 매겨짐

 

 

 

 

RANK, DENSE_RANK, ROW_NUMBER의 비교

(코드)

SELECT first_name, salary,
    RANK()OVER(ORDER BY salary DESC) AS RANK,
    DENSE_RANK()OVER(ORDER BY salary DESC) AS DENSE_RANK,
    ROW_NUMBER()OVER(ORDER BY salary DESC) AS ROW_NUMBER
FROM employees;

 

 

 

 

--ROWNUM

(1,2,3,4,5,6,7,.... )

SELECT ROWNUM, employee_id, first_name  
FROM employees
WHERE ROWNUM <= 10;

 

 

이중조건 설정시, 

WHERE ROWNUM > 10 AND ROWNUM <= 20;

는 안됨

--> 아래와 같이

--1. DATA 설정(급여 순위) = 정렬
--2. ROWNUM 설정
--3. 범위 설정
SELECT RNUM, employee_id, first_name, salary        --3. 범위설정
FROM (SELECT ROWNUM AS RNUM, employee_id, first_name, salary --2. 번호를 붙임
FROM (SELECT employee_id, first_name, salary
        FROM employees
        ORDER BY salary DESC)--1. SORTING DATA만들기
        )
WHERE RNUM > 10 AND RNUM <= 20;