4장 함수 : 02 단일 행 함수 - 5 일반함수

NVL 함수

SELECT salary * commission_pct
FROM employees
WHERE commission_pct is NOT NULL
ORDER BY commission_pct

commission_pct 값이 NULL 이면 1로 치환해  salary 와 곱해주고 싶다면?

NVL(열이름, 치환 값)

사용할것. MSSQL 에서는 ISNULL 함수와 같은기능.

SELECT salary * NVL(commission_pct,1)
FROM employees
ORDER BY commission_pct

 

---

DECODE 함수

DECODE(열 이름, 조건 값 1, 치환 값 1, 조건 값 2, 치환 값 2, …, 기본값) 형태로 조건 논리를 계속 이어 나갈 수 있습니다.

산술 계산 외에 문자열 표현도 가능

SELECT first_name,
    last_name,
    department_id,
    salary 원래월급,
    DECODE(department_id, 60 , salary*1.1, salary) 조정된월급,
    DECODE(department_id, 60 , '10%인상', '미인상') 인상여부
FROM employees;

 

https://gent.tistory.com/374

[MSSQL] IIF 함수 사용법 (CASE, DECODE)

https://omty.tistory.com/35

[MSSQL] IIF 함수를 사용한 간단한 조건문 작성하기

---

CASE 표현식

CASE

      WHEN 조건 1 THEN 출력 값 1

      WHERE 조건 2 THEN 출력 값 2

      …

      ELSE 출력 값 3

END

SELECT employee_id, first_name, last_name, salary,

       CASE

            WHEN salary >= 9000 THEN ‘상위급여’

            WHEN salary BETWEEN 6000 AND 8999 THEN ‘중위급여’

            ELSE ‘하위급여’

       END AS 급여등급

FROM  employees

WHERE job_id = ‘IT_PROG’;

위 처럼 아니고 아래처럼  END AS 에 컬럼값을 매겨주지 않으면 컬럼값이 저렇게 길게 뜬다

SELECT employee_id, first_name, last_name, salary,
    CASE WHEN salary >= 9000 THEN '상위급여'
         WHEN salary BETWEEN 6000 and 8999 THEN '중'
       ELSE '하' END
FROM employees 
WHERE job_id = 'IT_PROG';

---

---

SELECT A.employee_id,

       A.department_id,

       B.department_name,

       salary,

       RANK()       OVER(PARTITION BY A.department_id ORDER BY salary DESC) RANK_급여,

       DENSE_RANK() OVER(PARTITION BY A.department_id ORDER BY salary DESC) DENSE_RANK_급여,

       ROW_NUMBER() OVER(PARTITION BY A.department_id ORDER BY salary DESC) ROW_NUMBER_급여

FROM employees A, departments B

WHERE A.department_id = B.department_id

ORDER BY B.department_id, A.salary DESC;