[Database] Sub Query / UNION

실습 ) 회원 별 구매이력 중 구매금액이 가장 높은 금액의 상품명을 추출하여 회원아이디와 이메일과 함께 조회 하시오.

SELECT
	m.m_id							AS '회원아이디'
	,m.m_email						AS '이메일'
	,g.g_name						AS '상품명'
	,max(o.o_amount * g.g_price)	AS '구매금액'
FROM 
	tb_member AS m
	INNER join
	tb_order AS o
	on
	o.o_id = m.m_id
	INNER join
	tb_goods AS g
	on
	o.o_g_code = g.g_code

GROUP BY m.m_id
ORDER BY 구매금액 DESC;	

 

---

Sub Query

• SQL문 내에서 하위에 존재하는 쿼리 ( SQL문 안에 SQL )

예)

	SELECT
	*
FROM
	tb_goods AS g
WHERE
	g.g_price > ( SELECT
						ROUND(AVG(g.g_price),0)
					FROM
						tb_goods AS g ) ;

 

스칼라 서브쿼리	인라인 뷰	서브쿼리
SELECT 문에 있는 서브쿼리 (1행만 반환)	FROM 절에 있는 서브쿼리	WHERE 절에 있는 서브쿼리

인라인 뷰 : FROM절에 있는 서브쿼리

SELECT
   g.g_code
  ,g.g_name
  ,g.g_price
  ,g.g_seller_id
  ,g.g_reg_date
FROM
  tb_goods AS g
  JOIN
  ( SELECT
      ROUND(AVG(g.g_price), 0) AS '평균단가'
    FROM
      tb_goods AS g) AS gAvg
WHERE
  g.g_price > gAvg.평균단가;

스칼라 서브쿼리 : SELECT문에 있는 서브쿼리

 

SELECT
   g.g_code
  ,g.g_name
  ,g.g_price
  ,g.g_seller_id
  ,g.g_reg_date
FROM
  (SELECT
     *
    ,( SELECT
          ROUND(AVG(gAvg.g_price), 0)
       FROM
          tb_goods AS gAvg) AS '평균단가'
   FROM
     tb_goods ) AS g
WHERE
   g.g_price > g.평균단가;

서브쿼리 : WHERE절에 있는 서브쿼리

SELECT
   g.g_code
  ,g.g_name
  ,g.g_price
  ,g.g_seller_id
  ,g.g_reg_date
FROM
   tb_goods AS g
WHERE
   g.g_price > ( SELECT
                    ROUND(AVG(g.g_price), 0)
                 FROM
                    tb_goods AS g );

단일 행 서브쿼리

SELECT
  *
FROM
  tb_member AS m
WHERE
  m.m_level = ( SELECT
                  l.level_num
                FROM
                  tb_member_level AS l
                WHERE
                  l.level_num = 1 ); 

다중 행 서브쿼리

SELECT
  *
FROM
  tb_member AS m
WHERE
  m.m_level IN ( SELECT
                  l.level_num
                FROM
                  tb_member_level AS l ); 

다중컬럼 서브쿼리

SELECT
  *
FROM
  tb_member AS m1
WHERE
  (m1.m_id, m1.m_level) IN ( SELECT
                                m2.m_id
                               ,MIN(m2.m_level)
                             FROM
                               tb_member AS m2 );

---

UNION

*** SELECT의 개수가 동일해야 한다!

• 여러 개의 쿼리의 합집합
• 여러 개의 SQL문을 합쳐 하나의 SQL문으로 만들어주는 방법

UNION VS . UNION ALL

• UNION : 여러 개의 쿼리의 중복 값을 제거한 결과
• UNION ALL : 여러 개의 쿼리 결과가 중복이 되더라도 전부 결과에 반영
• 속도 :  UNOIN ALL >  UNION

규칙

• 각 SELECT의 컬럼의 수가 같아야 한다.
• 컬럼명이 같아야 한다. ( 같지 않을 경우 alias를 사용하여 같게 만듦)
• 컬럼별 데이터타입이 같아야 한다. ( 반환 가능한 형태 )
• 하나의 ORDER BY만 사용한다.
• SELECT문들의 순서는 상관없다.

---

실습

 

실습1. DML - 1부터 시작하는 행의 번호를 포함하여 상품테이블을 출력하시오.

SELECT
	@rowNum:=(@rowNum+1) AS '행 번호'
	,g.*
FROM
	tb_goods AS g
	,(SELECT @rowNum:=0) r
	
ORDER BY	g.g_price DESC;

실습2. DML - 상품테이블의 단가 중 단가가 높은 상품 순서대로 순위를 포함하여 상품테이블을 출력하시오.

SELECT
	CASE
	WHEN (@gPri := g.g_price) THEN @rank:=(@rank+1)
	WHEN (@gPri = g.g_price) THEN @rank
	END AS '순위'
	,g.*
FROM
	tb_goods AS g
	,(SELECT @rank:=0, @gPri:=null) r
	
ORDER BY	g.g_price DESC;

실습3. DML - 관리자 제외 회원 별 로그인 평균 횟수보다 많이 로그인한 회원 아이디와 로그인 횟수를 조회 하시오.

SELECT
	l.login_id AS '아이디'
	,COUNT(l.login_id) AS '로그인횟수'
FROM
	tb_login AS l
	INNER JOIN 
	tb_member AS m
	ON 
	m.m_id =  l.login_id
	INNER JOIN
	tb_member_level AS ml
	ON 
	ml.level_num = m.m_level
WHERE
	ml.level_name NOT LIKE '%관리자%'
GROUP BY l.login_id
HAVING COUNT(l.login_id) > (SELECT 
											ROUND(AVG(cAvg.로그인횟수),1) AS '평균로그인횟수'
									 FROM
										(SELECT
											l.login_id AS '아이디'
											,COUNT(l.login_id) AS '로그인횟수'
										 FROM
											tb_login AS l
											INNER JOIN 
											tb_member AS m
											ON 
											m.m_id =  l.login_id
											INNER JOIN
											tb_member_level AS ml
											ON 
											ml.level_num = m.m_level
										 WHERE
											ml.level_name NOT LIKE '%관리자%'
										 GROUP BY l.login_id ) AS cAvg);