SQL 정리

나의 Mysql 정리 노트!

CASE - WHEN 절

SELECT animal_id, name, 
case when sex_upon_intake like 'Intact%' 
then 'X' else 'O' end as 중성화
from animal_ins;

Order By에 연산자 사용 가능

SELECT ins.animal_id, ins.name
from animal_ins as ins join animal_outs as outs
on ins.animal_id = outs.animal_id
order by outs.datetime - ins.datetime desc
limit 2;

테이블 join 및 using

SELECT ins.animal_id, ins.name
from animal_ins as ins join animal_outs as outs
on ins.animal_id = outs.animal_id
where ins.datetime > outs.datetime
order by ins.datetime;

group by, having

SELECT id, name, host_id
from places
where host_id in (select host_id 
                  from places 
                  group by host_id 
                  having count(host_id) >= 2)
order by id;

hour 폼 변환

SELECT hour(datetime) HOUR, count(*) count
from animal_outs a
group by hour
having hour >= 9 and hour < 20
order by hour

ifnull(col, OO)

SELECT animal_type, ifnull(name, 'No name'), sex_upon_intake
from animal_ins
order by animal_id;