[sql] 우유와 요거트 모두 산 사람 찾기 with tmp를 이용해서 품

https://programmers.co.kr/learn/courses/30/lessons/62284

코딩테스트 연습 - 우유와 요거트가 담긴 장바구니

-- 코드를 입력하세요
WITH tmp as (
    SELECT *
    FROM cart_products
    WHERE NAME = 'Milk'
)

SELECT distinct cart_id
FROM tmp 
WHERE tmp.cart_id in (
    SELECT cart_id
    FROM cart_products
    WHERE NAME='Yogurt'
)

맞아서 신기했음.. 

ㅋㅋㅋㅋㅋ

 

 

더 깔끔한 모범코드들 

SELECT DISTINCT CART_ID
FROM CART_PRODUCTS
WHERE NAME = 'Yogurt' AND CART_ID IN (
    SELECT CART_ID
    FROM CART_PRODUCTS
    WHERE NAME = 'Milk'
)
ORDER BY CART_ID;

SELECT a.cart_id
from cart_products as a, cart_products as b
where a.cart_id=b.cart_id and a.name='milk' and b.name='yogurt'
order by a.cart_id;

 

https://programmers.co.kr/questions/20479

프로그래머스

3가지 방법 있다고 함 

 

SELECT cart_id from(select cart_id, group_concat(distinct name) as NAME 
from cart_products group by cart_id) st
 where NAME like '%Milk%' and NAME like '%Yogurt%'
 order by cart_id;