Case when문

Case when 문 

with table1 as (
   SELECT user_id, `point`, 
         (case when pu.`point` > 10000 then '1만 이상'
               when pu.`point` > 5000 then '5천 이상'
               else '5천 미만' end) as lv
         from point_users pu
)

SELECT a.lv, COUNT(*) as cnt FROM table1 a
group by a.lv

 

Quiz
1. point_users 테이블에서 평균이상 포인트를 가지고 있으면 잘하고 있어요. 낮으면 열심히 합시다 표시

SELECT point_user_id, `point`, (
       case when `point` > (select AVG(`point`) from point_users) then '잘 하고 있어요!'
            else '열심히 합시다!' end
      ) as msg
 from point_users pu

2. users 테이블에서 이메일 도메인별 유저의 수 세어보기

SELECT SUBSTRING_INDEX(email,'@', -1) as domain, COUNT(*) from users u
group by domain

-- 위와 아래 동일

SELECT domain, COUNT(*) as cnt from (
   SELECT SUBSTRING_INDEX(email,'@', -1) as domain from users u
) a 
group by domain

 

3. enrolled_detail 테이블의 enrolled_id별 전체 강의 수와 들은 강의수(done) 출력하기

with table1 as (
   SELECT enrolled_id, COUNT(*) as done_cnt from enrolleds_detail
   WHERE done = 1
   group by enrolled_id
), table2 as (
   SELECT enrolled_id, COUNT(*) as total_cnt
   from enrolleds_detail
   group by enrolled_id 
)
select a.enrolled_id, 
       a.done_cnt, 
       b.total_cnt,
       ROUND(a.done_cnt/b.total_cnt, 2) as ratio
 from table1 a
 inner join table2 b on a.enrolled_id = b.enrolled_id