Kosta 클라우드 네이티브 애플리케이션 개발 (CNA) 교육 6일차

CASE WHEN THEN

CASE
	WHEN (조건절) THEN '값'
	[ELSE]           '값2'
END

예제

• 1

select comm, 
	case 
    	when comm is null then 0 
        else comm 
     end as comms 
 from emp; 
--- select comm, decode(comm, null, 0, comm)as comms from emp;

• 2

select ename, deptno, decode(deptno,10,'십', 
						decode(deptno,20,'이십', 
							decode(deptno, 30, '삼십') ) ) as deptnoStr 
from emp order by deptno asc; 
                                    
                                    
                                    
select ename, deptno, decode(deptno, 10,'십' ,20,'이십' ,30, '삼십' ) as deptnoStr 
from emp order by deptno asc;

함수

• RANK : 순서를 매기는 함수(중복 가능)
  • dense_rank : 순서를 매기는 함수(중복X)

  select sal,
      rank() over (order by sal desc) as rk
  from emp;
  ------------------------------
  select sal,
      dense_rank() over (order by sal desc) as rk
  from emp;
  ------------------------------
  --아에 겹치는 숫자 없이
  select sal,
      ROW_NUMBER() over (order by sal desc) as rk
  from emp;

• ROWNUM : 테이블 입력 순서 번호가 출력되는 함수

select rownum, sal, ename from emp order by sal desc;

• 그룹별 순서

select deptno, sal, 
rank() over (PARTITION by deptno order by deptno asc, sal desc) as rk 
from emp;

 

• 합집합

insert into A values(1,'A'); insert into A values(2,'A'); 
insert into A values(3,'A'); insert into B values(3,'B'); 
insert into B values(4,'B'); insert into B values(5,'B'); 

(select num from a) union (select num from b); 

--- -> 1,2,3,4,5 (select num from a) union all (select num from b); 
--- -> 1,2,3,3,4,5

• 교집합

(select num from a) intersect (select num from b); --- -> 3

• 뺄셈

(select num from a) minus (select num from b); --- -> 1,2

• 페이징

select ss.* from (select rownum as rnum, s.* 
from (select rownum as init_rum, ename, hiredate 
		from emp order by hiredate desc) s ) ss 
where ss.rnum between 6 and 10; 
        
 ------------------ select s.* 
from (select ename, hiredate,
ROW_NUMBER() over (order by hiredate desc) as rk from emp) s 
where rk between 6 and 10; 

------- 같은 쿼리