우유와 요거트가 담긴 장바구니

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

프로그래머스

[문제]

데이터 분석 팀에서는 우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지 알아보려 합니다. 우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 SQL 문을 작성해주세요. 이때 결과는 장바구니의 아이디 순으로 나와야 합니다.

 

[나의 풀이]

SELECT CART_ID
FROM CART_PRODUCTS
WHERE NAME IN ('YOGURT', 'MILK')
GROUP BY CART_ID
HAVING COUNT(DISTINCT NAME) = 2
ORDER BY 1;

 

[다른 풀이]

1)

WITH M AS (SELECT DISTINCT CART_ID, NAME
FROM CART_PRODUCTS
WHERE NAME = 'Milk')

, Y AS (SELECT DISTINCT CART_ID, NAME
FROM CART_PRODUCTS
WHERE NAME = 'Yogurt')

SELECT Y.CART_ID
FROM Y
INNER JOIN M
ON Y.CART_ID = M.CART_ID
ORDER BY Y.CART_ID

2)

SELECT CART_ID
FROM (
    SELECT CART_ID, GROUP_CONCAT(NAME) AS NAMES
    FROM CART_PRODUCTS
    GROUP BY CART_ID
) TMP

WHERE NAMES LIKE '%Milk%' 
    AND NAMES LIKE '%Yogurt%'

만약 NAMES까지 같이 SELECT하게 된다면?

SELECT CART_ID, NAMES
FROM (
    SELECT CART_ID, GROUP_CONCAT(NAME) AS NAMES
    FROM CART_PRODUCTS
    GROUP BY CART_ID
) TMP

WHERE NAMES LIKE '%Milk%' 
    AND NAMES LIKE '%Yogurt%'

이런 식으로 끝도 없이 나옴

 

*TIP) GROUP_CONCAT(DISTINCT NAME)하면 중복되는 값 한 번만 나옴

SELECT CART_ID, NAMES
FROM (
    SELECT CART_ID, GROUP_CONCAT(DISTINCT NAME) AS NAMES
    FROM CART_PRODUCTS
    GROUP BY CART_ID
) TMP

WHERE NAMES LIKE '%Milk%' 
    AND NAMES LIKE '%Yogurt%'

 

 

-참고

https://velog.io/@jokwed/%EC%9A%B0%EC%9C%A0%EC%99%80-%EC%9A%94%EA%B1%B0%ED%8A%B8%EA%B0%80-%EB%8B%B4%EA%B8%B4-%EC%9E%A5%EB%B0%94%EA%B5%AC%EB%8B%88SummerWinter-Coding2019

[프로그래머스][MySQL]우유와 요거트가 담긴 장바구니(Summer/Winter Coding(2019) )

 

3)

SELECT CART_ID
FROM CART_PRODUCTS
GROUP BY CART_ID
HAVING SUM(CASE WHEN NAME = 'Milk' THEN 1 ELSE 0 END) >= 1 
   AND SUM(CASE WHEN NAME = 'Yogurt' THEN 1 ELSE 0 END) >= 1
ORDER BY CART_ID;