프로그래머스 SQL 고득점 Kit 정리

나머지 문제는 풀만했다.
조금 푸는데 고민했던 문제들을 뽑아봤다.

중복 제거하기

DISTINCT 를 COUNT 안에 사용 -> (중복데이터 제거) + (NULL인 데이터 제외)

SELECT COUNT(DISTINCT NAME) as count 
FROM ANIMAL_INS 
WHERE NAME IS NOT NULL

입양 시각 구하기

솔직히 이런 쿼리를 왜 짜는지 모르겠다..

방법1(무식)

select a.HOUR, ifnull(b.COUNT,0) as COUNT
FROM (
    select 0 as hour union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union
    select 7 union select 8 union select 9 union select 10 union select 11 union select 12 union
    select 13 union select 14 union select 15 union select 16 union select 17 union select 18 union
    select 19 union select 20 union select 21 union select 22 union select 23
    ) a LEFT JOIN (
        select HOUR(datetime) as HOUR, COUNT(HOUR(datetime)) as COUNT
        from animal_outs
        GROUP BY HOUR 
     ) b
ON a.hour = b.HOUR

방법2

set @hour = -1;
select
    (@hour := @hour+1) as HOUR,
    (select count(*) from ANIMAL_OUTS WHERE @hour = HOUR(DATETIME)) as COUNT
FROM ANIMAL_OUTS
WHERE @hour < 23;

없어진 기록 찾기

OUTER JOIN 후 NULL인 데이터 찾기

SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_OUTS O LEFT JOIN ANIMAL_INS I ON O.ANIMAL_ID  = I.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID

보호소에서 중성화한 동물

보호소에서 입양되어 나간(OUTS) 테이블과 보호소에 들어온(INS) 테이블에서 중성화 여부 변화 비교

SELECT I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME
  FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID 
  -- WHERE I.SEX_UPON_INTAKE != O.SEX_UPON_OUTCOME
  WHERE I.SEX_UPON_INTAKE LIKE 'Intact%' 
    AND O.SEX_UPON_OUTCOME NOT LIKE 'Intact%'
  ORDER BY I.ANIMAL_ID