MySQL 코딩공부 2

문제 : https://programmers.co.kr/learn/challenges?tab=all_challenges

22.헤비 유저가 소유한 장소
서브쿼리 사용

select * from places 
where host_id in
(select host_id 
 from places group by host_id 
 having count(host_id)>=2)
order by id

셀프 조인

SELECT A.*
FROM PLACES A, PLACES B
WHERE A.ID <> B.ID AND A.HOST_ID = B.HOST_ID
GROUP BY A.ID
ORDER BY A.ID;

• MySQL 에서 <> 는 !=

조인

select A.* from places A
join (select host_id 
 from places group by host_id 
 having count(host_id)>=2) B
on A.host_id = B.host_id
order by A.id

23.오랜 기간 보호한 동물(2)

SELECT B.animal_id, B.name from animal_ins A 
join animal_outs B
on A.animal_id = B.animal_id
order by (B.datetime - A.datetime) desc
limit 2

24.우유와 요거트가 담긴 장바구니
group_concat 으로 milk, yogurt를 찾는 방식

SELECT C.CART_ID
FROM (
    SELECT CART_ID, GROUP_CONCAT(DISTINCT NAME) AS P
    FROM CART_PRODUCTS
    GROUP BY CART_ID) AS C
WHERE C.P LIKE '%Milk%Yogurt%' OR '%Yogurt%Milk%'
GROUP BY C.CART_ID
ORDER BY C.CART_ID;

각각 다른 테이블 뽑아서 서브쿼리 사용, 조인

SELECT DISTINCT A.CART_ID
 FROM 
     (
         SELECT CART_ID
         FROM CART_PRODUCTS
         WHERE NAME LIKE 'Milk'
     ) A
    INNER JOIN
    (
         SELECT CART_ID
         FROM CART_PRODUCTS
         WHERE NAME LIKE 'Yogurt'
     ) B
         ON A.CART_ID = B.CART_ID
 ORDER BY A.CART_ID;

좀 더 간단하게

select distinct A.cart_id
from cart_products A, cart_products B
where A.cart_id = B.cart_id
and A.name = 'Milk'
and B.name = 'Yogurt'
order by A.cart_id

25.보호소에서 중성화한 동물

select A.animal_id, A.animal_type, A.name from (select * from animal_ins
where sex_upon_intake like '%Intact%') A
join (select * 
     from animal_outs 
     where sex_upon_outcome like '%Spayed%'
     or sex_upon_outcome like '%Neutered%') B
using (animal_id)
order by animal_id

26.이름 el이 들어가는 동물 찾기

SELECT animal_id, name from animal_ins
where name like '%el%'
and animal_type = 'Dog'
order by name asc

27.중성화 여부 파악하기

SELECT animal_id, name, 
    if(sex_upon_intake like 'Neutered%' or sex_upon_intake like  'Spayed%', 'O', 'X') 
    as '중성화' 
from animal_ins
order by animal_id asc

28.datetime에서 date로 형 변환

SELECT animal_id, name, date_format(datetime, '%Y-%m-%d') as 날짜
from animal_ins
order by 1

29.루시와 엘라 찾기

SELECT animal_id, name, sex_upon_intake from animal_ins
where name in ('Lucy','Ella','Pickle','Rogan','Sabrina','Mitty')
order by animal_id

like보다 이럴 땐 in이 더 낫다.
이외에도 원하는 검색어가 컬럼에 있나 찾을 때
where 'column' like concat('%', '검색어', '%')