[ex23] pseudo, rownum

ex23_pseudo
    
    의사컬럼, Pseudo Column
    - 실제 컬럼이 아닌데 컬럼처럼 행동하는 요소
    
    rownum
    - 오라클 전용
    - row num > 행번호 > 레코드의 일련번호
    - from절이 실행될 때 각 레코드에 일련번호를 할당힌다.(*****)
    - where절의 영향을 받으면 일련번호를 다시 정비한다.(reindexing) (*****)
    - rownum을 사용 > 서브 쿼리를 자주 사용한다. 

 

select
    name, buseo,      --컬럼(속성) > output > 객체의 특성에 따라 다른 값을 가진다.(개인 데이터)
    sysdate,          --함수       > output > 모든 레코드가 동일한 값을 가진다.(정적 데이터)
    substr(name, 2),  --함수       > input + output > 객체마다 다른 값을 가진다.
    '상수',            --상수      > output > 모든 레코드가 동일한 값을 가진다.(정적 데이터)
    rownum             --의사 컬럼 > output > 객체의 특성에 따라 다른 값을 가진다.(개인 데이터)
from tblInsa;

---

-- 게시판 > 페이징
-- 1페이지 > rownum between 1 and 20
-- 2페이지 > rownum between 21 and 40
-- 3페이지 > rownum between 41 and 60

select name, buseo, rownum from tblInsa;
select name, buseo, rownum from tblInsa where rownum = 1; --위치로 검색
select name, buseo, rownum from tblInsa where rownum <= 5;

--아래 안나옴
select name, buseo, rownum from tblInsa where rownum = 10;
select name, buseo, rownum from tblInsa where rownum > 5;


select 
    name, buseo, rownum 
from tblInsa; --1. *** 이 시점의 데이터를 가지고 rownum이 이미 할당되어 있다. 


select 
    name, buseo, rownum     --3. 소비
from tblInsa                --1. 생성+(rownum)
    where rownum = 1;       --2. 조건

select
    name, buseo, rownum
from tblInsa                --1. 생성(+rownum)
    where rownum = 1;       -- rownum을 조건으로 사용 > 반드시 1~조건 범위 > 조건에 포함



--------------

select 
    name, buseo, basicpay, rownum
from tblInsa;


select 
    name, buseo, basicpay, rownum --rownum 순서대로 안나옴
from tblInsa
    order by basicpay desc;

-- 급여를 가장 많이 받는 직원 5명~


select 
    name, buseo, basicpay, rownum, rnum --바깥쪽의 rownum이 안쪽 rownum을 덮어쓰기
from (select 
            name, buseo, basicpay, rownum as rnum
        from tblInsa
            order by basicpay desc)
                where rownum <= 5;



-- 인구수가 가장 많은 나라 1~3등 
select * from tblCountry;

-- 1. 원하는 정렬
select * from tblCountry where population is not null order by population desc;
select rownum from tblCountry where population is not null order by population desc;

-- 2. 위의 결과셋을 가지고 한번더 rownum을 만들기 > from 절 실행
select * from (select * from tblCountry where population is not null order by population desc);
select rownum from (select * from tblCountry where population is not null order by population desc);
select * from (select * from tblCountry where population is not null order by population desc)
    where rownum <= 3;



-- tblInsa. 급여가 3등

-- 1. 급여 순으로 정렬 
select * from tblInsa order by basicpay desc;

select rownum from tblInsa order by basicpay desc;

-- 2. 원하는 순서대로 정렬 결과셋 > from절 적용 > rownum 다시 매긴다.
select a.*, rownum from (select * from tblInsa order by basicpay desc) a;

select a.*, rownum from (select * from tblInsa order by basicpay desc) a where rownum = 1;
select a.*, rownum from (select * from tblInsa order by basicpay desc) a where rownum <= 5;
select a.*, rownum from (select * from tblInsa order by basicpay desc) a where rownum = 3;

-- 3. 
--안쪽에 있는 rnum은 정적 데이터가 됨
select * from (select a.*, rownum as rnum from (select * from tblInsa order by basicpay desc) a)
    where rnum = 3;




-- tblComedian. 5번째 뚱뚱한 사람?
select * from tblComedian;

-- 1. 정렬
select * from tblComedian order by weight desc;

-- 2. 서브쿼리 > rownum 별칭
select a.*, rownum as rnum from (select * from tblComedian order by weight desc) a;

-- 3. 서브쿼리 > rownum 고정시키기 위해서
select * from (select a.*, rownum as rnum from (select * from tblComedian order by weight desc) a); --완성

-- 안쪽 rnum 사용해서 where 조건
select * from (select a.*, rownum as rnum from (select * from tblComedian order by weight desc) a)
    where rnum = 5;

 

---

오답노트

-- 3. tblInsa. 여자 인원수가 (가장 많은 부서 및 인원수) 가져오시오.
-- 정렬
select buseo, count(*) as c from tblInsa where substr(ssn, 8, 1) = '2' group by buseo order by c desc;
-- 서브쿼리 > rownum 별칭
select a.*, rownum as rnum from (select buseo, count(*) as c from tblInsa where substr(ssn, 8, 1) = '2' group by buseo order by c desc) a;
-- 서브쿼리 > rownum 고정
select buseo, c 
from (select a.*, rownum as rnum 
from (select buseo, count(*) as c  
        from tblInsa where substr(ssn, 8, 1) = '2' group by buseo order by c desc) a)
    where rnum = 1;
--답안
select * from (select buseo, count(*) as cnt from tblinsa where substr(ssn, 8, 1) = '2'
   group by buseo order by count(*) desc) where rownum = 1;
   

-- 9. tblinsa. 평균 급여 2위인 부서에 속한 직원들을 가져오시오. --***
-- 총무부
select buseo, round(avg(basicpay)) from tblInsa group by buseo order by avg(basicpay) desc;
select * from tblInsa group by buseo order by avg(basicpay) desc;
select a.*, rownum as rnum from (select buseo from tblInsa group by buseo order by avg(basicpay) desc) a;
select * from tblInsa where buseo = ();
select * from (select a.*, rownum as rnum from (select buseo, avg(basicpay) from tblInsa group by buseo order by avg(basicpay) desc) a) where rnum = 2;

--답
select * from (select a.*, rownum as rnum from (select buseo, avg(basicpay) from tblinsa
   group by buseo
      order by avg(basicpay) desc) a) where rnum = 2;
select * from tblInsa;



-- 11. tblinsa. 남자 직원 중에서 급여를 3번째로 많이 받는 직원과 9번째로 많이 받는 직원의 급여 차액은 얼마인가? ***
select * from tblInsa where substr(ssn, 8, 1) = '1' order by basicpay desc;
select a.*, rownum as rnum from (select * from tblInsa where substr(ssn, 8, 1) = '1' order by basicpay desc) a;

select (select basicpay from (select a.*, rownum as rnum from (select * from tblInsa where substr(ssn, 8, 1) = '1' order by basicpay desc) a) where rnum = 3) 
 - (select basicpay from (select a.*, rownum as rnum from (select * from tblInsa where substr(ssn, 8, 1) = '1' order by basicpay desc) a) where rnum = 9) as "급여 차액" from dual;

--답
select () - () from dual; --dual 이용해야 됨

select 
   (select basicpay from (select basicpay, rownum as rnum from (select basicpay from tblinsa where substr(ssn, 8, 1) = '1' order by basicpay desc)) where rnum = 3)
   -
   (select basicpay from (select basicpay, rownum as rnum from (select basicpay from tblinsa where substr(ssn, 8, 1) = '1' order by basicpay desc)) where rnum = 9) as "급여 차액"
from dual;