[MySQL] 프로그래머스 입양 시각 구하기(2) (SET, WITH RECURSIVE)

풀이1.

SET @hour := -1;
SELECT (@hour := @hour + 1) AS HOUR,
(SELECT Count(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @hour) AS COUNT
FROM ANIMAL_OUTS
WHERE @hour < 23

풀이2.

WITH RECURSIVE HOUR AS(
SELECT 0 AS h
UNION ALL
SELECT h+1 FROM HOUR WHERE h<23)
SELECT h AS HOUR, COALESCE(COUNT(ANIMAL_ID),0) AS COUNT
FROM HOUR LEFT JOIN ANIMAL_OUTS ON HOUR.h = DATE_FORMAT(DATETIME, '%k')
GROUP BY HOUR.h

 

programmers.co.kr/learn/courses/30/lessons/59413

코딩테스트 연습 - 입양 시각 구하기(2)