Oracle 기본정리 - 함수5(decode, case when)
Oracle 기본정리 - 함수5(decode, case when)
--decode(데이터, if, 값,
elseif,값,
elseif,값,
elseif,값.......)
--decode(데이터, if, 값1,값2......)
--IF가 맞으면 값1, 아니면 값2
--예제1
--부서코드
--10 영업
--20 기획
--30 개발
select empno, ename, decode(deptno,10,'영업'
,20,'기획'
,30,'개발') as result
from emp;
--예제2
select empno, ename, decode(trunc(sal/2000),0,'low'
,1,'mid'
,2,'high') as str_sal
from emp;
--문제1 10번부서의 급여는 10%인상, 20번부서는 20%인상 나머지 부서는 동결
--20번부서는 20%인상
--나머지 부서는 동결
select empno, ename, decode(deptno,10,sal+sal*0.1,20,sal+sal*0.2,sal) as upsal from emp;
--문제2 anaiyst 5%인상, salesman 10%인상, manager 15%인상, clerk 20%인상
select * from emp;
select empno, ename, JOB, decode(job,'anaiyst',sal*1.05,
'salesman',sal*1.1,
'manager',sal*1.15,
'clerk',sal*1.2,
sal) as upsal
from emp;
--when then end함수
--2가지방법
select case 컬럼명 when 컬럼의 해당하는 조건값1 then 값1
when 컬럼의 해당하는 조건값2 then 값2
when 컬럼의 해당하는 조건값3 then 값3
else 값
from 테이블;
select case when 컬럼=조건값1 then 값1
when 컬럼=조건값2 then 값2
when 컬럼=조건값3 then 값3
else 값
from 테이블;
--예제1
--부서코드
--10 영업
--20 기획
--30 개발
select deptno, case deptno when 10 then 'a'
when 20 then 'b'
when 30 then 'c'
end as result
from emp;
select case when deptno = 10 then 'a'
when deptno = 20 then 'b'
when deptno = 30 then 'c'
end as result
from emp;
--예제2
select empno, ename, sal, case when sal<2000 then 'low'
when sal<4000 then 'mid'
when sal>4000 then 'high'
END AS str_sal
from emp;
--문제1 anaiyst 5%인상, salesman 10%인상, manager 15%인상, clerk 20%인상
select empno, ename, job, case job WHEN 'ANALYST' THEN SAL*1.05 --첫번째 방법
WHEN 'SALESMAN' THEN SAL*1.1
WHEN 'MANAGER' THEN SAL*1.15
WHEN 'CLERK' THEN SAL*1.2
else sal
END AS UPSAL
FROM EMP;
select empno, ename, job, case WHEN job='ANALYST' THEN SAL*1.05 --두번째 방법
WHEN job='SALESMAN' THEN SAL*1.1
WHEN job='MANAGER' THEN SAL*1.15
WHEN job='CLERK' THEN SAL*1.2
else sal
END AS UPSAL
FROM EMP;
--문제2 06년도 입사, fullname , totalsal
-방법1
select employee_id, LAST_NAME|| ' ' ||first_name AS fullname, salary*(1+NVL(commission_pct,0)) as totalyearsal, hire_date from employees WHERE substr(HIRE_DATE,0,2) = '06'
-방법2
select employee_id, LAST_NAME|| ' ' ||first_name AS fullname, salary*(1+NVL(commission_pct,0)) as totalyearsal, hire_date from employees WHERE hire_date like '06%';
-방법3
select employee_id, LAST_NAME|| ' ' ||first_name AS fullname, salary*(1+NVL(commission_pct,0)) as totalyearsal, hire_date from employees WHERE to_char(hire_date,'YY') = '06';
-방법4
select employee_id, LAST_NAME|| ' ' ||first_name AS fullname, salary*(1+NVL(commission_pct,0)) as totalyearsal, hire_date from employees WHERE trunc(hire_date,'YY') = '06/01/01';
-방법5
select employee_id, LAST_NAME|| ' ' ||first_name AS fullname, salary*(1+NVL(commission_pct,0)) as totalyearsal, hire_date from employees WHERE hire_date between '06/01/01' and '06/12/31'
--부서 10번 부서의 인원과 나머지 인원을 출력
select * from emp;
select count(*) - count( decode(deptno,10,1) ) as d10,
count(case when deptno != 10 then 1 end) as result --방법1 --count( decode(deptno,10,null,1) ) as other 방법2
--count(*) - count( decode(deptno,10,1) ) as other 방법3
from emp;
--81년도 입사자 인원과 82년도 입사자 인원 나머지 인원을 출력
select * from emp;
select count(decode(to_char(hiredate,'yyyy'),'1981',1)) as d81,
count(decode(to_char(hiredate,'yyyy'),'1982',1)) as d82,
count(decode(to_char(hiredate,'yyyy'),'1981',null,'1982',null,1)) as Other
from emp;
--전체 인원 81년도 82년도 나머지인원
select COUNT(*),
count(decode(to_char(hiredate,'yyyy'),'1981',1)) as d81,
count(decode(to_char(hiredate,'yyyy'),'1982',1)) as d82,
count(decode(to_char(hiredate,'yyyy'),'1981',null,'1982',null,1)) as Other
from emp;
--전체 평균과 81년도 입사자의 급여평균, 82년도 입사자의 평균 급여를 출력
select trunc(avg(sal)) as "전체평균",
trunc(avg(decode(to_char(hiredate,'yyyy'),'1981',sal))) as d81,
trunc(avg(decode(to_char(hiredate,'yyyy'),'1982',sal))) as d82
from emp;