프로그래머스 SQL 고득점 GROUP BY

1. 고양이와 개는 몇 마리 있을까

# 합집합 풀이
SELECT animal_type, count(animal_type) 'count'
FROM animal_ins
WHERE animal_type = 'cat'
UNION

SELECT animal_type, count(animal_type) 'count'
FROM animal_ins
WHERE animal_type = 'dog'

# group by 풀이
SELECT animal_type, count(animal_type) 'count'
FROM animal_ins
GROUP BY animal_type
ORDER BY animal_type

2. 동명 동물 수 찾기

SELECT name, count(name)
FROM animal_ins
GROUP BY name
HAVING COUNT(name)>1
ORDER BY name ASC;

3. 입양 시각 구하기(1)

WITH count_hour as(
    SELECT HOUR(datetime) as hour
    FROM animal_outs
)

SELECT hour, COUNT(hour) as 'count'
FROM count_hour
WHERE hour between 9 and 19
GROUP BY hour
ORDER BY hour;

# 임시테이블 없어도 될 것 같음
SELECT HOUR(DATETIME) AS 'HOUR', COUNT(ANIMAL_ID) AS 'COUNT'
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) BETWEEN 9 AND 19
GROUP BY HOUR
ORDER BY HOUR

4. 입양 시각 구하기(2)

WITH RECURSIVE num(HOUR) AS (
    SELECT 0
    UNION ALL 
    SELECT HOUR+1 FROM num WHERE HOUR<23
)

SELECT A.HOUR, 
    CASE WHEN B.CNT IS NULL THEN 0 
    ELSE B.CNT END AS COUNT 
FROM num A
LEFT JOIN (SELECT HOUR(DATETIME) AS H, COUNT(ANIMAL_ID) AS CNT
            FROM ANIMAL_OUTS 
           GROUP BY 1) AS B ON A.HOUR = B.H
           
# 다른 코드
with recursive time as
(select 0 as hour union all select hour + 1 from time where hour < 23)

select hour, count(animal_id) count
from time
left join animal_outs on (hour = date_format(datetime, '%H'))
group by hour;

# 다른풀이
WITH RECURSIVE TEMP AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR+1 FROM TEMP
    WHERE HOUR<23
)

SELECT HOUR, COUNT(ANIMAL_OUTS.DATETIME) AS COUNT FROM

TEMP LEFT JOIN ANIMAL_OUTS
ON TEMP.HOUR = HOUR(ANIMAL_OUTS.DATETIME)

GROUP BY HOUR;