[7/100] 240418

https://m.blog.naver.com/gglee0127/221318160003

[Oracle] 순위를 반환하는 함수 (RANK, DENSE_RANK, ROW_NUMBER)

 

 

ERD약자 ?

 

물리적 = 실행 누르면 컴퓨터에 만들어질수 있는 상태

 

로지컬 = 

 

erd 점선과 실선 각 도형의 차이

식별 비식별 관계

 

/*
1981년 5월 31일 이후 입사자 중 커미션(COMM)이 NULL이거나 0인 사원의 커미션은 500으로
그렇지 않으면 기존 COMM을 출력하시오.
*/
select ename, case when nvl(comm,0) = 0 then 500 else comm end as COMM
from emp
where hiredate > '1981-05-31'
;

--decode
select ename, decode(nvl(comm,0),0,500,comm) as comm
from emp
where to_char(hiredate, 'YYYY/MM/DD') > '1981/05/31'
;

--80년도 입사자와 그외 출력
select count(case when hiredate>'1979-12-31' and hiredate<'1981-01-01' then empno end) as 입사1980,
count(case when hiredate<='1979-12-31' or hiredate>='1981-01-01' then empno end) as 그외
from emp
;

--가장 높은 급여를 받는 사원보다 입사일이 늦은 사람의 이름, 입사일 출력
select ename, hiredate
from emp
where hiredate > (select hiredate from emp where ((select max(sal) from emp) = sal))
;

--ford 보다 입사일이 늦은 사원 중 급여가 가장 높은 사원의 이름과 급여를 출력
--ford의 입사일보다 늦은 사원 뽑기//그중 급여가 가장 높은 사원의 이름과 급여
select ename, sal
from emp
where sal = (select max(sal) from emp where hiredate > (select hiredate from emp where ename = 'FORD'))
and ename != 'FORD'
;

-- 20번 부서의 최고 급여보다 많은 사원의 사원번호, 사원명, 급여 출력
select empno, ename, sal
from emp
where sal > (select max(sal) from emp where deptno = 20)
;

--emp테이블에서 가장 많은 사원을 갖는 mgr의 사원번호
select mgr empno, count(*)
from emp 
group by mgr
having count(*) = (select max(count(*))from emp group by mgr)
;

--ROWNUM
select * from (
select rownum rnum, ss.*
from(
select rownum, empno, ename, sal from emp
order by sal desc
) ss
) s
where s.rnum = 2
;

--급여가 두번째로 많은 사원의 이름과 급여를 출력
--rownum없이--
SELECT ename, sal
FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp WHERE sal < (SELECT MAX(sal)FROM emp));
--rownum 있이--
--rownum은 탑(1)에서부터 색인이 가능하기 때문에 직접 sort를 지정해주고 일반컬럼화 시켜서 
--찾아야지만 원하는대로 찾을 수 있다.
select s.*
from (
        select rownum snum, ss.*
        from (
              select rownum as inum, ename, sal
              from emp
              order by sal desc
             ) ss
     ) s
where snum = 2
;

--부가적인 사안
--row_number() over() as rnum
select row_number() over(order by e.sal) as snum
        , e.*
from emp e
order by e.sal
;

--그룹--over안에서는 group by가 아닌 partition by 그룹할컬럼명
select row_number() over(partition by job order by e.sal, ename) as snum
        , e.*
from emp e
;

--각 직업별 콩라인뽑기
select *
from ( select row_number() over(partition by job order by e.sal, e.ename) as snum
        , e.*
        from emp e
        )
        where snum = 2
        ;

--또 다른 방법 dense_rank
SELECT ename, sal
FROM (
    SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal DESC) AS rank
    FROM emp
) ranked_emp
WHERE rank = 2;

--입사일이 두 번째로 빠른 사람의 부서명과 이름, 입사일 출력
select d.dname, e.ename, e.hiredate
from emp e, dept d
where e.deptno = d.deptno and
      hiredate = (select min(hiredate) from emp where hiredate > (select min(hiredate) from emp));

select s.dname, s.ename, s.hiredate
from(
select rownum as rnum, ss.*
from (select d.dname, e.ename, e.hiredate
        from emp e, dept d
        where e.deptno = d.deptno
        order by hiredate asc
    ) ss
) s
where rnum = 2
;

--dallas에 위치한 부서에 최대 급여를 받는 사원과 최소급여를 받는 사람의 급여차이 출력
select (select max(sal) from emp where deptno = (select deptno from dept where loc = 'DALLAS'))
-(select min(sal) from emp where deptno = (select deptno from dept where loc = 'DALLAS')) sal
from dual
;

--
select max(sal) - min(sal) as saldiff
from
(
    select d.loc, e.sal
    from emp e, dept d
    where e.deptno = d.deptno
    and d. loc = 'DALLAS'
)
;