실습 ) 회원 별 구매이력 중 구매금액이 가장 높은 금액의 상품명을 추출하여 회원아이디와 이메일과 함께 조회 하시오.
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);
'Database' 카테고리의 다른 글
| [Database] 프로시저 (0) | 2020.04.13 |
|---|---|
| [Database] SQL VIEW (0) | 2020.04.06 |
| [Database] 정규화(Normalization) (0) | 2020.04.01 |
| [Database] 데이터베이스 설계 (0) | 2020.03.25 |
