프로그래머스 입양 시각 구하기(2) 풀이(MySQL)

사이트명 : 프로그래머스(Programmers)

문제명 : 입양 시각 구하기(2)

SQL : MySQL

 

이번 문제는 좀 어렵던데

풀긴 풀었지만 미개하게 풀었다

 

어쨌든 문제로 돌아가 0시부터 23시까지 시간대별로 행이 몇개씩 있는지 반환하는 문제인데

마지막에 조회 후 시간순으로 정렬을 걸어 반환해야 한다 

 

내 풀이방법은 아래와 같은데

 

보면 알겠지만 복붙을 난사해서 굉장히 참혹하다

각 시간대별로 COUNT를 조회한 뒤 UNION ALL을 통해 합친다

 

이후 ()로 감싸 서브쿼리로 만든 후 밖에서 0시와 23시에 해당하는 HOUR만 조회하고

스크린샷에서는 잘렸는데

 

ORDER BY HOUR로 시간대별로 정렬한 후 반환한다

이렇게 푸는거는 풀 줄 모를때 이렇게 푸는 것이고

 

뭔가 분명히 좋은 방법이 있을 것이다

다만 떠올리지 못했을 뿐..

 

프로그래머스에 바로 적용 가능한 코드는 아래와 같다

 

SELECT HOUR, COUNT
FROM
    (
    SELECT 0 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 0
    UNION ALL
    SELECT 1 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 1
    UNION ALL
    SELECT 2 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 2
    UNION ALL
    SELECT 3 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 3
    UNION ALL
    SELECT 4 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 4
    UNION ALL
    SELECT 5 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 5
    UNION ALL
    SELECT 6 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 6
    UNION ALL
    SELECT 7 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 7
    UNION ALL
    SELECT 8 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 8
    UNION ALL
    SELECT 9 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 9
    UNION ALL
    SELECT 10 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 10
    UNION ALL
    SELECT 11 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 11
    UNION ALL
    SELECT 12 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 12
    UNION ALL
    SELECT 13 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 13
    UNION ALL
    SELECT 14 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 14
    UNION ALL
    SELECT 15 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 15
    UNION ALL
    SELECT 16 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 16
    UNION ALL
    SELECT 17 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 17
    UNION ALL
    SELECT 18 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 18
    UNION ALL
    SELECT 19 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 19
    UNION ALL
    SELECT 20 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 20
    UNION ALL
    SELECT 21 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 21
    UNION ALL
    SELECT 22 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 22
    UNION ALL
    SELECT 23 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 23
    UNION ALL
    SELECT 24 AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = 24
    ) SQ1
WHERE HOUR >= 0
  AND HOUR <= 23
ORDER BY HOUR