(DB) 서브 쿼리(Sub Query)

서브 쿼리

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

 

 

스칼라 서브쿼리

- select문에 있는 서브쿼리 (1행만 반환)

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

 

인라인 뷰

- from절에 있는 서브쿼리 -> 집합

- 속도가 가장 빠름

SELECT
	g.g_name
	,g.g_price
FROM
	tb_goods AS g
	JOIN
	(	SELECT
			ROUND(AVG(g_price), 0) AS avg_price
		FROM
			tb_goods) AS g1 /* on절 쓸때 필요해서 as필요 */
WHERE
	g.g_price > g1.avg_price;

 

서브쿼리

- where절에 있는 서브쿼리

SELECT
	*
FROM
	tb_goods AS g
WHERE
	g.g_price > (	SELECT
						ROUND(AVG(g1.g_price), 0) AS '평균단가'
					FROM
						tb_goods AS g1);

 

단일행 서브쿼리

SELECT
	*
FROM
	tb_member AS m
WHERE
	m.m_level = (SELECT 
					l.level_num
				FROM 
					tb_member_level AS l
				WHERE /* where 조건을 안줘서 다중행이 나오게 되면 오류*/
					l.level_name = '관리자');

 

다중행 서브쿼리, 다중행 연산자

- in : (or)리턴되는 값 중에서 조건에 해당하는 값이 있으면 참

SELECT
	*
FROM
	tb_member AS m
WHERE
	m.m_level IN (	SELECT 
						l.level_num /*여기가 하나밖에 못옴*/
					FROM 
						tb_member_level AS l);

- any, some : (or) 앞에 비교 연산자 올 수 있음

SELECT
	*
FROM
	tb_member AS m
WHERE
	m.m_level > ANY(SELECT 
						l.level_num
					FROM 
						tb_member_level AS l
					WHERE
						l.level_num > 1);
/*
l.level_num > 2 or l.level_num > 3 or l.level_num > 4 라는 조건이 생김
결국 만족하는건 l.level_num =3인 경우
*/

- all : (and)

SELECT
	*
FROM
	tb_member AS m
WHERE
	EXISTS(	SELECT 
				l.level_num
			FROM 
				tb_member_level AS l
			WHERE
				l.level_num > 1);

- exists : 참, 거짓만 판별 , 비교 조건이 하나라도 존재한다면(or), in과 동일하지만 속도가 더 빠르다

SELECT
	*
FROM
	tb_member AS m
WHERE
	EXISTS(	SELECT 
				l.level_num
			FROM 
				tb_member_level AS l
			WHERE
				l.level_num > 1);

SELECT
	*
FROM
	tb_member AS m
WHERE
	EXISTS(	SELECT 
				l.level_num /*의미가 없음*/
			FROM 
				tb_member_level AS l
			WHERE
				l.level_num > 1 AND l.level_num = m.m_level);
/*AND l.level_num = m.m_level 앞 테이블과 연관있는 서브쿼리임을 조건으로 줘야함*/

 

 

실습1. mysql 행번호 출력하기 - 세션 변수 이용
(mysql 8버전 이상 window함수 row_number() 함수 사용가능 )

SELECT
	(@rowNum := @rowNum+1 ) AS 'row' ->select문 안에는 반복문이 도니깐
	,g.*
FROM
	tb_goods AS g
	JOIN 
	( SELECT @rowNum := 0) AS r;
    /* '='비교 연산자와 차이두기위해 ':='로 표기*/

>> 세션 변수를 select문으로 지정하면, 제일 겉 select문이 반복될때마다 증감되는 형식이므로 카운트번호로 쓸 수 있다.

 

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

(@gprice라는 변수를 추가해야한다고 힌트주심) 

SELECT
	(@rank := @rank + 1) AS 'rank'
	,g.g_code
	,g.g_name
	,g.g_price
FROM
	tb_goods AS g
	JOIN
	( SELECT @rank := 0) AS r;

 

풀이

SELECT	
	if(@gprice=g.g_price,@rank:=@rank,@rank:=@rank + 1) AS 'rank'
	,g.g_code
	,g.g_name
	,@gprice:=g.g_price AS 'g_price'
FROM
	tb_goods AS g
	JOIN
	( SELECT @rank := 0, @gprice := 0) AS r
ORDER BY g.g_price DESC;

>> 상품의 가격이 윗행의 가격과 같으면 @rank를 같은 값으로 메기고, 다르다면 1추가시키라는 조건을줌

select에 쓴 컬럼은 숨길 수 없다해서 그냥 as로 별칭줘서 g_price인척

 

선생님 풀이

SELECT	
	(CASE
	WHEN @gprice = g.g_price THEN @rank
	WHEN @gprice := g.g_price THEN @rank := @rank +1
	END) AS 'rank'
	,g.g_code
	,g.g_name
	,g.g_price
FROM
	tb_goods AS g
	JOIN
	( SELECT @rank := 0, @gprice := 0) AS r
ORDER BY g.g_price DESC;

>> if문으로 깔끔하지 않았떤 쿼리문도 훨씬 가독성 좋아지고, 필요없던 컬럼수도 줄어들었다

:= 는 대입연산자이지만 case문에서는 실행되면 true라고 판단하기 때문에 가장 윗줄의 조건이 거짓이고 대입연산자가 실행되어 원하는 결과값을 얻을 수 있다.

 

 

실습3. dense_rank가 아닌 rank로 (동점자 있을시 다음 순위는 동점자 수만큼 + 처리 ex. 1 2 3 3 5)

(@checkCount라는 변수를 추가해야한다고 힌트주심)

 

풀이

SELECT
	(@checkCount := @checkCount + 1) AS 'no'
	,(CASE
	WHEN @gprice = g.g_price THEN @rank 
	WHEN @gprice := g.g_price  THEN @rank := @checkCount
	END) AS 'rank'
	,g.g_code
	,g.g_name
	,g.g_price
FROM
	tb_goods AS g
	JOIN
	( SELECT @rank := 0, @gprice := 0, @checkCount := 0) AS r
ORDER BY g.g_price DESC;

>> 어떻게 해야할지 고민하다가 동점자가 나와서 rank가 중복되고 나면 다시 checkCount 번호로 돌아오면 될것같다는 생각이 들어서 case문으로 코드 짜봄

 

선생님 풀이

SELECT
	(CASE
	WHEN @gprice = g.g_price THEN @rank 
	WHEN @gprice := g.g_price  THEN @rank := @checkCount + 1
	END) AS 'rank'
	,g.g_code
	,g.g_name
	,g.g_price
	,(@checkCount := @checkCount + 1) AS checkCount
FROM
	tb_goods AS g
	JOIN
	( SELECT @rank := 0, @gprice := 0, @checkCount := 0) AS r
ORDER BY g.g_price DESC;

>> 선생님은 checkCount 컬럼을 rank 컬럼보다 뒤쪽에 둬서 조건문이 약간 다르지만 나와 접근한 방식은 같았다.

그리고 checkCount컬럼을 아예 없애고 싶으면 제일 겉 select문을 서브쿼리로 만드는 인라인뷰를 사용해서 select에 내가 원하는 rank,g_code,g_name,g_price값만 선택하면 된다고 알려주심

 

checkCount컬럼없애기

SELECT
	result.rank
	,result.g_code
	,result.g_name
	,result.g_price
FROM
(SELECT
	(CASE
	WHEN @gprice = g.g_price THEN @rank 
     /* @gprice가 0일때 false가 나옴.. else문 추가 */
	WHEN @gprice := g.g_price  THEN @rank := @checkCount + 1    
	ELSE 
		@rank := @checkCount + 1
	END) AS 'rank'
	,g.g_code
	,g.g_name
	,g.g_price
	,(@checkCount := @checkCount + 1) AS checkCount
FROM
	tb_goods AS g
	JOIN
	( SELECT @rank := 0, @gprice := 0, @checkCount := 0) AS r
ORDER BY g.g_price DESC) AS result;

>> 그런데 인라인뷰로 만들면 case문에서 대입연산자를 사용한게 문제가 생길 수 있기 때문에 else문으로 처음 dense_rank방식의 쿼리문을 한번 더 써줘야 완벽하게... 완성이다

 

 

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

SELECT
	m.m_id AS '회원아이디'
	,m.m_email AS '이메일'
	,g.g_name AS '상품명'
	,o.o_amount * g.g_price AS '구매금액'
FROM
	tb_member AS m
	inner JOIN 
	tb_order AS o
	ON 
	m.m_id = o.o_id
	inner JOIN 
	tb_goods AS g
	ON
	o.o_g_code = g.g_code
GROUP BY m.m_id;

>> 저번에도 틀렸던것같은데 이번에도 틀림..ㅎ

 

정답

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 
	m.m_id = o.o_id
	inner JOIN 
	tb_goods AS g
	ON
	o.o_g_code = g.g_code
GROUP BY m.m_id;

>> 데이터를 보지말고 의미를 생각하라고... 데이터에 중복값이 없고 정답이랑 똑같이 나와서 풀었다고 좋아하고 있었다...^^

실습1. 회원 별 로그인 평균 횟수보다 많이 로그인한 회원 아이디와 로그인 횟수를 조회 하시오

/* 평균 횟수 구하는 방법*/
select 
	avg(result.loginCnt) as '평균로그인횟수'
from 
		(SELECT
			m.m_id as '회원 아이디'
			,m.m_name as '회원 이름'
			, count(l.login_id) as loginCnt
		FROM
			tb_member AS m
			INNER join
			tb_login AS l
			on 
			m.m_id = l.login_id
			and
			m.m_level > 1
		group by m.m_id) as result;

>> 평균 횟수도 못구하고 있어서 선생님이 코드를 짜줌... 처음에 having절도 써보고 서브쿼리를 where절에도 써보고 했는데 못함 

 

 선생님 풀이

SELECT
	m.m_id AS '회원아이디'
	,m.m_name AS '회원이름'
	,COUNT(1) AS '로그인횟수'
FROM
	tb_member as m
	INNER join
	tb_login AS l
	on
	m.m_id = l.login_id
	and
	m.m_level >1
GROUP BY m.m_id
HAVING 로그인횟수 >(SELECT
						avg(result.loginCnt) AS avgloginCnt
					from
						(SELECT
							count(1) as loginCnt
						FROM
							tb_member AS m
						INNER join
							tb_login AS l
						on 
							m.m_id = l.login_id
						and
							m.m_level > 1
						group by m.m_id) AS result);

>> having절을 이용해서 조건문 써주기... 서브쿼리를 같은 코드를 반복해서 쓰는걸 이해 못했음 

---

변수 활용해서 풀어보기 ▼힌트

cnt	logincnt	sumlogincnt
1	3	3
2	7	10
3	5	15
4	7	22
5	4	26

 

풀이

SELECT
	re.m_id AS '회원아이디'
	,re.m_name AS '회원이름'
	,re.loginCnt AS '로그인횟수'
	,(@cnt := @cnt+1) AS cnt
	,(@sumlogincnt := @sumlogincnt+re.loginCnt) AS sumlogincnt
FROM
	(SELECT
		m.m_id
		,m.m_name
		,COUNT(1) AS loginCnt
	FROM
		tb_member AS m
		INNER join
		tb_login AS l
		on
		m.m_id = l.login_id 
		AND 
		m.m_level >1		
	GROUP BY l.login_id) AS re
	JOIN
	(SELECT @cnt:= 0, @sumlogincnt:=0) AS cnt
GROUP BY re.m_id
HAVING re.loginCnt >= MAX(sumlogincnt/cnt);

>>처음에 세션 변수를  AS re 셀렉트문 안에 넣어서 1,2,3,4 증가하는게 아니라 loginCnt와 똑같이 나와서 해결을 못하고 있었는데 선생님이 지금 1행으로 못보고 있으니깐 이행(세션변수 선언행)을 따로 빼서 1행으로 나오게 해야한다고 말해주셔서 풀었다...

 

선생님 풀이 > 필요없는 컬럼 인라인뷰로

SELECT
	result.m_id
	,result.m_name
	,result.loginCnt
FROM
	(SELECT
		re.m_id
		,re.m_name
		,re.loginCnt
		,(@cnt := @cnt+1) AS cnt
		,(@sumlogincnt := @sumlogincnt+re.loginCnt) AS sumlogincnt
	FROM
		(SELECT
			m.m_id
			,m.m_name
			,COUNT(1) AS loginCnt
		FROM
			tb_member AS m
			INNER join
			tb_login AS l
			on
			m.m_id = l.login_id 
			AND 
			m.m_level >1		
		GROUP BY l.login_id) AS re
		JOIN
		(SELECT @cnt:= 0, @sumlogincnt:=0) AS cnt) AS result
WHERE
	result.loginCnt > (@sumLoginCnt / @cnt);

 

 

실습2. 회원 별 구매이력 중 구매금액이 가장 높은 상위 30%만 조회하시오.

풀이1

SELECT
	result.m_id AS '아이디'
	,result.orderprice AS '구매액'
FROM
	(SELECT
		r1.m_id
		,r1.orderprice
		,@rank:=@rank+1 AS rank
	FROM
		(SELECT
			m.m_id
			,max(o.o_amount*g.g_price) AS orderprice
		FROM
			tb_member AS m
			INNER join
			tb_order AS o
			on
			m.m_id = o.o_id
			INNER join
			tb_goods AS g
			on
			o.o_g_code = g.g_code
		GROUP BY m.m_id) AS r1
		JOIN
		(SELECT @rank :=0) AS r2
	ORDER BY r1.orderprice desc) AS result
WHERE result.rank <= @rank*0.3;

>> 상위 30% 뽑는걸 못해서 where절 쓰는데 한참 걸림... 1. 문제에서 테이블을 확인 잘하기 tb_member 테이블 굳이 필요없음 2. 동점자 체크 안함 으로 다시 풀어보기...

 

풀이2

SELECT
	result.orderId
	,result.amt
FROM
	(SELECT
		o1.orderId
		,o1.amt
		,(case
			when @amtValue = o1.amt then @rank
			when @amtValue := o1.amt then @rank := @checkCnt+1
			else
				@rank := @checkCnt+1
			end) AS rank	
		,@checkCnt:=@checkCnt+1
	FROM
		(SELECT
			o.o_id AS orderId
			,SUM(o.o_amount*g.g_price) AS amt
		FROM
			tb_order AS o
			INNER join
			tb_goods AS g
			on
			o.o_g_code = g.g_code
		GROUP BY o.o_id
		ORDER BY amt DESC) AS o1
		JOIN
		(SELECT @rank:=0, @amtValue:=0, @checkCnt:=0) AS v) as result
WHERE result.rank < @rank*0.3;

>> 이 문제는 동점자가 없기 때문에 첫문제 풀이와 답이 똑같이 나왔음