✅ 코드 SELECT CART_ID FROM CART_PRODUCTS WHERE NAME IN ('Milk', 'Yogurt') GROUP BY CART_ID HAVING COUNT(DISTINCT NAME) = 2 ORDER BY CART_ID GROUP BY와 HAVING문을 사용한 풀이입니다. 핵심은 HAVING문에서 DISTINCT 키워드를 사용했다는 점입니다. 🔎 참고 WITH MILK_YOGURT_CARTS AS ( SELECT CART_ID , CASE WHEN SUM(IF(NAME IN ('Milk', 'Yogurt'), 1, 0)) = 2 THEN 'O' ELSE 'X' END AS TARGET FROM CART_PRODUCTS GROUP BY CART_ID ) SELECT CART_ID..
✅ 코드 SELECT P.PRODUCT_ID AS PRODUCT_ID , P.PRODUCT_NAME AS PRODUCT_NAME , SUM(P.PRICE * O.AMOUNT) AS SALES FROM FOOD_PRODUCT AS P INNER JOIN FOOD_ORDER AS O ON P.PRODUCT_ID = O.PRODUCT_ID WHERE O.PRODUCE_DATE BETWEEN '2022-05-01 00:00:00' AND '2022-05-31 23:59:59' GROUP BY PRODUCT_ID ORDER BY SALES DESC , PRODUCT_ID INNER JOIN → 5월 생산 식품목록 필터링(WHERE) → GROUP BY 순으로 풀이가 가능합니다.
LEFT JOIN과 서브쿼리를 사용한 2가지 풀이가 가능합니다. 서브쿼리를 사용한 코드부터 살펴보겠습니다. SELECT N , CASE WHEN P IS NULL THEN 'Root' WHEN N NOT IN (SELECT DISTINCT P FROM BST WHERE P IS NOT NULL) THEN 'Leaf' ELSE 'Inner' END AS node_type FROM BST ORDER BY N; 4번째 줄의 WHERE문을 입력하지 않으면 원하는 대로 코드가 출력되지 않습니다. 이 부분을 이해하느라 상당히 많은 시간이 소요됐는데 원인은 컬럼 P에 있는 NULL 때문입니다. NULL 값을 다룰 때는 항상 주의해야 할 것 같습니다. IS NOT NULL 조건이 없으면 Leaf 노드여야 할 노드들이..
✅ 코드 SELECT H.hacker_id , H.name FROM Submissions AS S INNER JOIN Challenges AS C ON S.challenge_id = C.challenge_id INNER JOIN Difficulty AS D ON C.difficulty_level = D.difficulty_level INNER JOIN Hackers AS H ON S.hacker_id = H.hacker_id WHERE D.score = S.score AND D.difficulty_level = C.difficulty_level GROUP BY H.hacker_id , H.name HAVING COUNT(S.submission_id) > 1 ORDER BY COUNT(S.submissio..
✅ 코드 SELECT IF(G.Grade < 8, NULL, S.Name) , G.Grade , S.Marks FROM Students AS S INNER JOIN Grades AS G ON S.Marks BETWEEN G.Min_Mark AND G.Max_Mark ORDER BY G.Grade DESC, S.Name, S.Marks INNER JOIN 기준을 BETWEEN문을 사용해서 세웠습니다.
✅ 코드 SELECT MONTH(START_DATE) AS MONTH , CAR_ID , COUNT(HISTORY_ID) AS RECORDS FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE CAR_ID IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE MONTH(START_DATE) IN (8, 9, 10) GROUP BY CAR_ID HAVING COUNT(HISTORY_ID) >= 5) AND MONTH(START_DATE) IN (8, 9, 10) GROUP BY MONTH, CAR_ID HAVING COUNT(HISTORY_ID) >= 1 ORDER BY MONTH , CAR_ID DESC; 우선적으로 ..
✅ 코드 SELECT CAR_ID , CASE WHEN SUM(IF('2022-10-16' BETWEEN START_DATE AND END_DATE, 1, 0)) = 0 THEN '대여 가능' ELSE '대여중' END AS AVAILABILITY FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY GROUP BY CAR_ID ORDER BY CAR_ID DESC; 그룹화를 수행한 뒤에 CASE문이 수행됩니다.
✅ 코드 SELECT C.CAR_ID AS CAR_ID , C.CAR_TYPE AS CAR_TYPE , FLOOR(C.DAILY_FEE * 30 * (1 - P.DISCOUNT_RATE/100)) AS FEE FROM CAR_RENTAL_COMPANY_CAR AS C INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P ON C.CAR_TYPE = P.CAR_TYPE AND P.DURATION_TYPE = '30일 이상' AND C.CAR_TYPE IN ('세단', 'SUV') LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H ON C.CAR_ID = H.CAR_ID AND H.END_DATE >= '2022-11-01' AND H...
