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;
'SQL' 카테고리의 다른 글
LEFT JOIN, INNER JOIN , 차집합 (0) | 2022.06.27 |
---|---|
UDF(User Define Function), Delimiter (0) | 2022.06.27 |
GROUP BY, Having 실습 (0) | 2022.06.27 |
숫자형데이터 실습 (0) | 2022.06.27 |
쿼리실행순서 (0) | 2022.06.27 |