Sub Query

2011-08-05 13:10:11

---

SQL 4 Oracle 오라클용 sql 실무
▒ Sub Query 

 

### 서브쿼리란 메인 Query안의 다른 Query
### 스칼라서브쿼리 : SELECT 절에 오는 Sub Query
### 인라인 뷰 : FROM 절 뒤에 오는 Sub Query(가상의 테이블을 만드는 Sub Query)
### 서브쿼리 : WHERE 절 뒤에 오는 Query
### 상관쿼리 : 메인 Query 서브Query간에 서로 참조작용하는 Query

 

▒ 상관쿼리
select a.고객ID   ,
     (select a1.고객ID
      from 목표_Table a1
      where a1.고객상태='활동중'
          and a1.고객ID=a.고객ID
     ) B     ,
     (select a2.고객ID
      from 목표_Table a2
      where a2.고객상태='활동중'
          and a2.고객ID in (select a21.고객ID
                                   from 목표_Table a21
                                  where a21.고객상태='활동중'
                                      and a21.고객ID=a.고객ID)
     ) C     ,
    (select a3.고객ID
     from 목표_Table a3
     where a3.고객상태='활동중'
         and a3.고객ID in (select a31.고객ID
                                   from 목표_Table a31
                                  where a31.고객상태='활동중'
                                      and a31.고객ID in (select a32.고객ID
                                                                 from 목표_Table a32
                                                                where a32.고객상태='활동중'
                                                                    and a32.고객ID=a.고객ID) 
                      )
    )  D
from 목표_Table a
where a.고객상태='활동중'

 

▒ 상관쿼리2
select a.고객ID,
  (select a1.고객ID
   from 목표_Table a1
   where a1.고객상태='활동중'
       and a1.고객ID=a.고객ID) B
  ,   
  (select (select a2.고객ID
              from 목표_Table a2
              where a2.고객상태='활동중'
                  and a2.고객ID = a1.고객ID)
   from 목표_Table a1
   where a1.고객상태='활동중'
       and a1.고객ID=a.고객ID) C
   ,
   (select (select (select a3.고객ID
                          from 목표_Table a3
                         where a3.고객상태='활동중'
                           and  a3.고객ID = a2.고객ID)
               from 목표_Table a2
               where a2.고객상태='활동중'
                   and a2.고객ID=a1.고객ID)
    from 목표_Table a1
    where a1.고객상태='활동중'
        and a1.고객ID=a.고객ID) D
from 목표_Table a
where a.고객상태='활동중'

 

▒ 인라인 뷰
select a.고객ID, B.고객ID, C.고객ID, D.고객ID
from 목표_Table a,
   (select a1.고객ID
    from 목표_Table a1
    where a1.고객상태='활동중') B
    ,   
    (select (select a2.고객ID
             from 목표_Table a2
             where a2.고객상태='활동중'
                 and a2.고객ID = a1.고객ID)
     from 목표_Table a1
     where a1.고객상태='활동중') C
     ,      
     (select (select (select a3.고객ID
                            from 목표_Table a3
                           where a3.고객상태='활동중'
                              and a3.고객ID = a2.고객ID)
              from 목표_Table a2
              where a2.고객상태='활동중'
                 and a2.고객ID = a1.고객ID)
      from 목표_Table a1
      where a1.고객상태='확동중') D     
where a.고객상태='활동중'
  and a.고객ID=b.고객ID
  and a.고객ID=c.고객ID
  and a.고객ID=d.고객ID