[SQL문제풀기] 월별 잡은 물고기 수 구하기

문제

MYSQL

내가 작성한 정답

#1
SELECT COUNT(*) FISH_COUNT, MONTH(TIME) MONTH
FROM FISH_INFO
GROUP BY MONTH(TIME)
ORDER BY 2;

#2
SELECT COUNT(*) FISH_COUNT, DATE_FORMAT(TIME,'%m')+0 MONTH
FROM FISH_INFO
GROUP BY DATE_FORMAT(TIME,'%m')+0
ORDER BY 2;

#3 - 1월부터 12월까지 전부 출력할 경우
WITH RECURSIVE MONTHS AS (
    SELECT 1 MONTH
    UNION
    SELECT MONTH +1
    FROM MONTHS
    WHERE MONTH <12
)
SELECT COUNT(ID) FISH_COUNT, M.MONTH
FROM FISH_INFO F
RIGHT JOIN MONTHS M ON MONTH(F.TIME) = M.MONTH
GROUP BY M.MONTH
ORDER BY 2;