rownum, 서브쿼리

rownum
==> select에 의해 검색된 결과에 
차례대로 순번을 매겨주는 가상컬럼입니다.

==> select에 의해 검색된 결과로 부터 
일부분만 잘라내기 위하여 rownum을 사용합니다.

ex) 모든 사원의 이름을 출력, 순번을 차례대로 매겨서 출력
select rownum, ename from emp;

ex) 사원의 이름 3명만 출력
select rownum, ename from emp where rownum <=3;

select  ename from emp where rownum <=3;


ex) 가장 가격의 비싼 도서의 정보를 출력
select * from book
where rownum =1 
order by price desc;

BOOKID BOOKNAME             PUBLISHER     PRICE
------ -------------------- ---------- --------
     1 축구의 역사          굿스포츠      7,000

   
   ===> 원하는 결과를 얻을 수 없어요!
    
    where절을 먼저 실행해요. 그러니까 그냥 원래 데이터에서 1개 뽑아와서
    정렬하는 거에요!
    
  order by 한 것에 rownum 조건식을 사용하려면
   ==> 반드시 서브쿼리를 사용해야 합니다!
   order by 로 정렬한 select의 결과를 from절의 가상의 테이블로 두고 
   사용해야 합니다.

 
  select * from book order by price desc;
  
  select * from (select * from book order by price desc)
   where rownum = 1;

BOOKID BOOKNAME             PUBLISHER     PRICE
------ -------------------- ---------- --------
    13 재미있는 오라클      쌍용미디어   50,000

서브쿼리
SQL문장안에 포함되는 또다른 SQL문을 말합니다.

서브쿼리의 위치에 따라 부르는 이름
select 절 스칼라 서브쿼리
from 절 인라인 뷰
where 중첩 서브쿼리


동작방식에 따라 부르는 이름
상관 서브쿼리: 메인쿼리와 조건식이 필요한 경우
비상관 서브쿼리: 메인쿼리와 조건식이 필요없는 경우


반환하는 레코드의 수의 따라 부르는 이름
단일행 서브쿼리: 서브쿼리의 결과가 1건인 경우
다중행 서브쿼리: 서브쿼리의 여러건 인 경우

스칼라 서브쿼리의 실습
==> select절에 서브쿼리가 오늘 것을 말합니다.

-  고객번호별로 총구액을 출력
select custid, sum(saleprice) from orders group by custid;

- 고객별로 총구매액을 출력
고객번호, 고객이름, 총구매액을 출력
select c.custid, name, sum(saleprice)
from customer c, orders o
where c.custid = o.custid 
group by c.custid, name;


select custid, (select name from customer c where c.custid = o.custid ) name  , sum(saleprice)
from orders o
group by custid;

인라인 뷰
==> from 절에 오는 서브쿼리를 말하며
from절에는 원래 테이블이름이 와야하는데
검색한 결과를 가상의 테이블로 보고 사용한다고 해서 
인라인 뷰라고 합니다.
                    

ex) 고객번호가 2이하인 고객의 이름과 총판매액을 출력
select name, sum(saleprice) 
from customer c, orders o
where c.custid = o.custid and
c.custid <= 2 
group by name;

select name, sum(saleprice) 
from (select custid, name from customer where custid <=2) c, orders o
where c.custid = o.custid 
group by name;

중첩 서브쿼리
==> 서브쿼리 where절에 조건식에 오는 경우를 말합니다.

이때에는 서브쿼리의 건수가 단일행인지 아닌지에 따라 
단일행연산자 다중행연산자를 구분하여 사용해야 합니다.

=, >, <, >=, <=, != 
이러한 연산자는 서브쿼리의 건수가 단일행 일때만 사용할 수 있어요!

서브쿼리의 건수가 다중행 일때에는 
= 대신에 in을 써야하고
!= 대신에 not in을 써야하고 
>, <, >=, <= 는
다중행 연산자인  any(some), all 과 같이 사용해야 합니다.
또, 서브쿼리 건수가 있는지 없는지만 판단하려면  exists, not exists를 사용합니다.

ex) 평균 주문금액 이하의 주문에 대하여 주문번호와 주문금액을 출력

select orderid, saleprice 
from orders
where saleprice <=  ( select avg(saleprice) from orders );

select avg(saleprice) from orders;


ex) 각 고객의 평균 주문금액보다 큰 금액의 주문내역에 대하여 주문번호, 고객번호, 주문금액을 출력
select orderid, custid, saleprice 
from orders o1
where saleprice >  ( select avg(saleprice) from orders o2 where o1.custid  = o2.custid  )
order by custid;


select custid, avg(saleprice) from orders group by custid order by custid;

** 중첩서브쿼리의 결과가 다중행 일때의 실습

단일행 연산자를 사용할 수 없으며
다중행 연산자 in, not in, any(some), all을 사용해야 합니다.

ex) 대한민국에 거주하는 고객에게 판매한 도서의 총판맥을 출력

select sum(saleprice) from orders 
where custid =  (select custid from customer where address like '%대한민국%');

SQL> select sum(saleprice) from orders
  2  where custid =  (select custid from customer where address like '%대한민국%');
where custid =  (select custid from customer where address like '%대한민국%')
                 *
2행에 오류:
ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.

===> 서브쿼리의 건수가 여러건일 때는 다중행 연산자를 사용해야 합니다!

select sum(saleprice) from orders 
where custid  in  (select custid from customer where address like '%대한민국%');


ex)  박지성이 구매하지 않은 도서정보를 출력합니다.
select * from book 
where bookid 
not in (select bookid from orders where custid 
= (select custid from customer where name = '박지성'));


ex)  박지성이 구매한 도서와 동일한 도서를 구매한 고객의 정보를 출력
select * from customer 
where custid in (   select  distinct custid from orders 
where bookid in (select bookid from orders where custid 
= (select custid from customer where name = '박지성'))  ) and 
name != '박지성';

select bookid from orders where custid 
= (select custid from customer where name = '박지성');


select  distinct custid from orders 
where bookid in (select bookid from orders where custid 
= (select custid from customer where name = '박지성'));

 

**
select ~~~~ --> A
minus
select ~~~ --> B
==> A에서 B만큼을 제외하고 출력

 

 

**
select ~~~~
union
select ~~~

ex)
박지성과구매성향이 가장비슷한사람의 구매목록
minus
박지성의구매목록


select * from book
where bookid in (select bookid from orders
where custid = ( select custid 
from (select custid, count(bookid)
from orders where 
bookid in (select bookid from orders where custid = (select custid from customer where name = '박지성'))
and custid != (select custid from customer where name = '박지성')
group by custid
order by count(bookid) desc) 
where rownum = 1)
minus
select bookid from orders where custid = (select custid from customer where name='박지성'));