DML 실습_db

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

SELECT
	(@rowNum := @rowNum +1) AS 'row'
	,g.*
FROM
	tb_goods AS g
JOIN
	(SELECT @rowNum := 0) AS r

 

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

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;

 

2. 답

SELECT
	result.rank,
	result.g_code,
	result.g_name,
	result.g_price
FROM(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 'check'
FROM
	tb_goods AS g
JOIN
	(SELECT @rank := 0, @gprice :=0, @checkCount :=0) AS r
ORDER BY g.g_price DESC) AS result;

 

3.
WHERE문으로

SELECT
	(
		SELECT
			COUNT(1) + 1
		FROM
			tb_goods AS g1
		WHERE
			
	)AS 'rank'
	,g.g_code
	,g.g_name
	,g.g_price
FROM
	tb_goods AS g
ORDER BY rank DESC;

 

3. 답

SELECT
	(
		SELECT
			COUNT(1) + 1
		FROM
			tb_goods AS g1
		WHERE
			g1.g_price > g.g_price
	)AS 'rank'
	,g.g_code
	,g.g_name
	,g.g_price
FROM
	tb_goods AS g
ORDER BY rank;

 

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

SELECT
	o.o_id AS '회원 아이디',
	m.m_email AS '이메일',
	g.g_name AS '상품명',
	Max(g.g_price*o.o_amount) 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
	g.g_code = o.o_g_code
GROUP BY o.o_id
ORDER BY g.g_price DESC;

 

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

SELECT
	*
from
	(SELECT
		(case 
		when @gPrice = result.amt then @rank
		when @gPrice := result.amt then @rank := @checked + 1
		END ) AS rank
		,result.o_id
		,result.amt
		,@checked := @checked +1 AS cnt
	from
		(SELECT
			o.o_id
			,SUM(g.g_price*o.o_amount) 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 result
		join
		(SELECT @gPrice := 0, @rank := 0, @checked := 0) AS r) AS total
WHERE
	total.rank <= (@checked * 0.3);

 

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

SELECT
      m.m_id,
      m.m_name,
      COUNT(1) AS 'loginCnt'
      FROM
         tb_login AS l
         INNER JOIN
         tb_member AS m
         on 
         l.login_id = m.m_id
         AND
         m.m_level>1
         GROUP BY l.login_id
         HAVING COUNT(1) > (SELECT
      AVG(result.loginCnt) AS '평균로그인횟수'
   FROM
      (SELECT
         COUNT(1) AS 'loginCnt'
      FROM
         tb_login AS l
         INNER JOIN
         tb_member AS m
         on 
         l.login_id = m.m_id
         AND
         m.m_level>1
         GROUP BY l.login_id) AS result);