서브쿼리 실습

  서브쿼리 실습

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

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

 

 

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

SELECT
  (case
    when (@cValue = g.g_price) then @rowNum
    when (@cValue := g.g_price) then (@rowNum := @rowNum+1)
  END )AS '순위'
  ,g.*
FROM
  tb_goods AS g
  ,(SELECT @rowNum:=0, @cValue:=NULL) r
  ORDER BY g.g_price DESC;

 

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

SELECT
  l.login_id AS '아이디',
  COUNT(l.login_id) AS'로그인횟수'
FROM
  tb_login AS l
WHERE
  l.login_id NOT LIKE '%id001%'
GROUP BY l.login_id
HAVING COUNT(l.login_id) > (SELECT
                            	round(AVG(avgLc.로그인횟수),1) AS '평균로그인횟수'
                            FROM
                            (SELECT
                              l.login_id AS '아이디',
                              COUNT(l.login_id) AS'로그인횟수'
                            FROM
                            	tb_login AS l
                            WHERE
                            	l.login_id NOT LIKE '%id001%'
                            GROUP BY l.login_id) AS avgLc) ;

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);