IF, SUM IF, IFNULL, CASE WHEN

IF
SELECT절에 사용
SELECT IF(조건, 참일때값, 거짓일때값)

IFNULL

IS NULL은 데이터가 NULL인지 아닌지 확인

IFNULL(컬럼, NULL일때값)
SELECT절에서 사용하는 함수

 

IF

mission 1)
SELECT name, IF(number<150, 'old','new') AS age
FROM mypokemon;

 

mission 2)
SELECT name, IF(attack+defense<100, 'weak','strong') AS ability
FROM mypokemon;

 

mission 3)
SELECT type, IF(AVG(attack)>=60, True, False) AS is_strong_type
FROM mypokemon
GROUP BY type;

 

mission 4)
SELECT name, IF(attack>100 AND defense>100, True, False) AS ace
FROM mypokemon;

 

 SUM (IF) 

select user_id
 from (
        SELECT user_id
               , count(1) as cnt1
              , sum( IF( item='device', 1, 0)) as cnt2
        FROM tb_payment
        group by  user_id
        ) AS tmp
where cnt1 = cnt2

 

모든 고객의 id, 총 캐시 구매액, 총 기기 구매액 표시

(구매를 한번도 하지 않은 유저도 결과에 포함)

SELECT A.id as user_id
, SUM (IF( B.item='cash', B.price, 0)) as  캐시금액
, SUM (IF( B.item='device', B.price, 0)) as 기기금액
FROM tb_user AS A
LEFT OUTER JOIN tb_payment AS B 
(ON A.id=B.user_id)
group by A.id

 

CASE WHEN

mission 5)
SELECT name,
CASE
WHEN number<100 THEN '<100'
WHEN number<200 THEN '<200'
WHEN number<500 THEN '<500'
END AS number_bin
FROM mypokemon;

 

mission 6)
SELECT name,
CASE
WHEN attack>=50 AND number>=150 THEN 'new_strong'
WHEN attack<50 AND number>=150 THEN 'new_weak'
WHEN attack>=50 AND number<150
THEN 'old_strong'
WHEN attack<50 AND number<150 THEN 'old_weak'
END AS age_attack
FROM mypokemon;

 

mission 7)
SELECT name, type, CASE
WHEN COUNT(1)=1 THEN 'solo'
WHEN COUNT(1)<3 THEN 'minor'
ELSE 'major'
END AS count_by_type
FROM mypokemon
GROUP BY type;