프로그래머스 SQL 고득점 KIT

몇몇 문제의 답안 입니다. (다 푸시고 보세요!!)

 

 

 

1. group by > 입양시각 구하기 (1)

SELECT hour(DATETIME) as 'hour', count(hour(DATETIME)) as 'count'
from ANIMAL_OUTS
GROUP BY hour( DATETIME )
HAVING hour >= 9 and hour <= 19
order by hour

 

 

2. group by > 입양시각 구하기 (2)

select b.hour, ifnull(a.count, 0) as 'count' from 
(SELECT hour(DATETIME) as 'hour', count(hour(DATETIME)) as 'count'
from ANIMAL_OUTS
GROUP BY hour( DATETIME )) as a
right outer join (SELECT 0 as hour,  0 as 'count' 
UNION SELECT 1 as hour,  0 as 'count'
UNION SELECT 2 as hour,  0 as 'count'
UNION SELECT 3 as hour,  0 as 'count'
UNION SELECT 4 as hour,  0 as 'count'
UNION SELECT 5 as hour,  0 as 'count'
UNION SELECT 6 as hour,  0 as 'count'
UNION SELECT 7 as hour,  0 as 'count'
UNION SELECT 8 as hour,  0 as 'count'
UNION SELECT 9 as hour,  0 as 'count'
UNION SELECT 10 as hour,  0 as 'count'
UNION SELECT 11 as hour,  0 as 'count'
UNION SELECT 12 as hour,  0 as 'count'
UNION SELECT 13 as hour,  0 as 'count'
UNION SELECT 14 as hour,  0 as 'count'
UNION SELECT 15 as hour,  0 as 'count'
UNION SELECT 16 as hour,  0 as 'count'
UNION SELECT 17 as hour,  0 as 'count'
UNION SELECT 18 as hour,  0 as 'count'
UNION SELECT 19 as hour,  0 as 'count'
UNION SELECT 20 as hour,  0 as 'count'
UNION SELECT 21 as hour,  0 as 'count'
UNION SELECT 22 as hour,  0 as 'count'
UNION SELECT 23 as hour,  0 as 'count') as b 
on a.hour = b.hour 

 

 

3. join > 없어진 기록 찾기

SELECT ao.ANIMAL_ID, ao.NAME
from ANIMAL_INS as ai
right join ANIMAL_OUTS as ao on ai.ANIMAL_ID = ao.ANIMAL_ID
where ai.ANIMAL_ID is null
order by ao.ANIMAL_ID

 

 

4. string, Date > 중성화 여부 파악하기

SELECT ai.ANIMAL_ID, ai.NAME, 
( case
 when ai.SEX_UPON_INTAKE like 'Neutered%' then 'O'
 when ai.SEX_UPON_INTAKE like 'Spayed%' then 'O'
 else 'X'
 end
) as '중성화'
from ANIMAL_INS as ai
order by ai.ANIMAL_ID;

 

 

5. string, Date > 오랜 기간 보호한 동물(2)

SELECT ai.ANIMAL_ID, ai.NAME
from ANIMAL_INS as ai
inner join ANIMAL_OUTS as ao on ai.ANIMAL_ID = ao.ANIMAL_ID
order by DATEDIFF(ai.DATETIME, ao.DATETIME)
limit 2;

 

 

 

흠...대략 1분 이상 걸린 문제들의 답지입니다. (함수를 몰라서... 시간이 걸린...)

2번 입양시각 구하기 (2)는 다른 방법이 없는건가... 쿼리가 너무 더럽네요. (for문을 사용을 못하게 막다니!!)

나머지 문제는....... 몇초컷이네요.

몇몇 문제는 재밌네요 ㅎㅎ 수고~