기타함수

SELECT jikwon_no, jikwon_name, jikwon_pay from jikwon ORDER BY jikwon_pay desc;
SELECT jikwon_no, jikwon_name, jikwon_pay,
RANK() OVER (ORDER BY jikwon_pay),
DENSE_RANK() OVER (ORDER BY jikwon_pay DESC) FROM jikwon ; -- 오름 차순
SELECT jikwon_no, jikwon_name, jikwon_ibsail,
RANK() OVER (ORDER BY jikwon_pay DESC),
DENSE_RANK() OVER (order by jikwon_pay DESC) FROM jikwon ; -- 내림 차순
SELECT jikwon_no, jikwon_name, jikwon_ibsail,
RANK() OVER (ORDER BY jikwon_ibsail DESC),
DENSE_RANK() OVER (order by jikwon_ibsail DESC) FROM jikwon ;

null 관련 함수

nvl(value, value2) : value1이 null이면 value2를 사용.

SELECT jikwon_name, jikwon_jik, nvl(jikwon_jik,'임시직'),
jikwon_pay, nvl(jikwon_pay,0) FROM jikwon;

nvl2(vlaue1, value2, value3) : value1이 null인지 평가 후 null이면 value3을 취함 아니면 value2를 취함

SELECT jikwon_name, nvl2(jikwon_jik,'정규직','임시직') AS jik,
nvl2 (jikwon_pay, jikwon_pay, 0) AS pay FROM jikwon;

nullif(value1,value2) : 두 개의 값이 일치하면 null을 일치하지 않으면 value1을 취함

SELECT NULLIF(LENGTH('abcd'),LENGTH('123')) AS result;
SELECT NULLIF(LENGTH('abc'),LENGTH('123')) AS result;
SELECT jikwon_name, jikwon_jik, NULLIF(jikwon_jik, '대리') FROM jikwon; -- 대리는 null이 됨

조건 표현식(conditional expresion)

형식1) case 표현식 when 비교값1 then 결과값1 when 비교값2 then 결과값2 [else 결과값 n] end as 별명

SELECT case 10 / 5 when 5 then '안녕' when 2 then 'hello' ELSE '잘가' END AS 결과 FROM DUAL; -- FROM DUAL 생략가능
SELECT jikwon_name, case jikwon_pay when 3000 then '연봉 3000' when 3500 then '연봉 3500' ELSE '기타연봉' END AS result FROM jikwon
WHERE jikwon_jik = '사원';
SELECT jikwon_name, jikwon_pay, jikwon_jik,
case jikwon_jik when '이사' then jikwon_pay 0.05 when '부장' then jikwon_pay 0.04 when '과장' then jikwon_pay 0.03
ELSE jikwon_pay 0.02 END AS donation
FROM jikwon;

형식2) case when 조건 then ~

SELECT jikwon_name,
case when jikwon_gen = '남' then 'M' when jikwon_gen = '여' then 'F' END AS gender FROM jikwon;
SELECT jikwon_name, jikwon_pay,
case when jikwon_pay >= 7000 then '우수' when jikwon_pay >= 5000 then '보통' ELSE '저조' END AS result
FROM jikwon WHERE jikwon_jik IN ('사원', '대리', '과장');

if(조건) 참값, 거짓값 as 별명

SELECT jikwon_name, jikwon_pay, jikwon_jik,
if( TRUNCATE(jikwon_pay / 1000, 0) >= 5, 'good', 'normal') AS result FROM jikwon;