오라클(SQL)단일형 서브쿼리

단일형 서브쿼리

테이블 다운로드

 

    -서브쿼리의 결과가 1개의 생으로 구성된경우
    -관계연산자가 사용된경우(조건)
    
사용예)회원테이블에서 회원의 평균 마일리지보다 많은 마일리지를 보유한 회원정보
        ALIAS는 회원번호, 회원명, 마일리지, 평균마일리지

SELECT  MEM_ID AS 회원번호,  
        MEM_NAME AS 회원명,  
        MEM_MILEAGE AS 마일리지,  
        (SELECT ROUND(AVG(MEM_MILEAGE)) 
         FROM   MEMBER) AS 평균마일리지         
FROM    MEMBER 
WHERE   MEM_MILEAGE >= (SELECT AVG(MEM_MILEAGE) 
                        FROM MEMBER); 

                        
사용예)

     

SELECT  MEM_ID AS 회원번호,  
        MEM_NAME AS 회원명,  
        MEM_MILEAGE AS 마일리지,  
        (SELECT ROUND(AVG(MEM_MILEAGE)) 
         FROM   MEMBER) AS 평균마일리지         
FROM    MEMBER 
WHERE   MEM_MILEAGE >= (SELECT SUM(MEM_MILEAGE) 
                        FROM MEMBER); 

 모든 회원별 구매현황을 조회하시오
    ALIAS는 회원번호, 회원명, 구매수량합계, 구매금액합계

SELECT  B.MEM_ID AS 회원번호,  
        B.MEM_NAME AS 회원명,  
        NVL(D.SQTY ,0) AS 구매수량합계,  
        NVL(D.SAMT ,0) AS 구매금액합계 
FROM    MEMBER B ,(SELECT   CART_MEMBER AS DID, 
                            SUM(CART_QTY) AS SQTY, 
                            SUM(CART_QTY*PROD_PRICE) AS SAMT 
                    FROM    CART, PROD 
                    WHERE   CART_PROD=PROD_ID 
                    AND     CART_NO LIKE '200506%' 
                    GROUP   BY CART_MEMBER)D 
WHERE   B.MEM_ID = D.DID(+); 

서브쿼리 : 2005년 6월 회원별 구매현황)

SELECT CART_MEMBER, 
        SUM(CART_QTY) AS SQTY, 
        SUM(CART_QTY*PROD_PRICE) AS SAMT 
FROM    CART, PROD 
WHERE   CART_PROD=PROD_ID 
AND     CART_NO LIKE '200506%' 
GROUP   BY CART_MEMBER;