✅ 코드 WITH RECURSIVE cte AS ( SELECT 0 AS num UNION ALL SELECT num + 1 FROM cte WHERE num < 23 ), ANIMAL_OUTS_REFACTOR AS ( SELECT HOUR(DATETIME) AS HOUR , COUNT(ANIMAL_ID) AS CNTS FROM ANIMAL_OUTS GROUP BY HOUR ORDER BY HOUR ) SELECT cte.num , IFNULL(O.CNTS, 0) FROM cte LEFT JOIN ANIMAL_OUTS_REFACTOR AS O ON cte.num = O.HOUR WITH RECURSIVE CTE문을 사용한 풀이입니다. NULL값을 다루기 위해서 IFNULL 함수를 사용했습니다.
✅ 코드 # 리뷰를 가장 많이 작성한 회원 WITH MOST_REVIEWS AS ( SELECT MP.MEMBER_ID AS MEMBER_ID , MP.MEMBER_NAME AS MEMBER_NAME , COUNT(DISTINCT RR.REVIEW_ID) AS REVIEWS FROM MEMBER_PROFILE AS MP INNER JOIN REST_REVIEW AS RR ON MP.MEMBER_ID = RR.MEMBER_ID GROUP BY MP.MEMBER_ID , MP.MEMBER_NAME ORDER BY REVIEWS DESC LIMIT 1 ) SELECT MR.MEMBER_NAME AS NAME , RR.REVIEW_TEXT AS REVIEW_TEXT , DATE_FORMAT(RR.REVIEW_D..
✅ 코드 SELECT CONCAT( '/home/grep/src/' , BOARD_ID , '/' , FILE_ID , FILE_NAME , FILE_EXT) AS FILE_PATH FROM USED_GOODS_FILE WHERE BOARD_ID = (SELECT BOARD_ID FROM USED_GOODS_BOARD ORDER BY VIEWS DESC LIMIT 1) ORDER BY FILE_ID DESC; WHERE문 단일행 서브쿼리를 사용한 풀이입니다.
✅ 코드 # GENDER 컬럼 NULL값 제외 WITH GENDER_INFO_Y AS ( SELECT USER_ID , GENDER FROM USER_INFO WHERE GENDER IS NOT NULL ) SELECT YEAR(SALES_DATE) AS YEAR , MONTH(SALES_DATE) AS MONTH , UI.GENDER AS GENDER , COUNT(DISTINCT UI.USER_ID) AS USERS FROM GENDER_INFO_Y AS UI INNER JOIN ONLINE_SALE AS OS ON UI.USER_ID = OS.USER_ID GROUP BY YEAR , MONTH , GENDER ORDER BY YEAR , MONTH , GENDER WITH문으로 우선 성별 컬럼의 ..
✅ 코드 # 서울에 위치한 레스토랑 리스트 WITH REST_IN_SEOUL AS ( SELECT * FROM REST_INFO WHERE ADDRESS LIKE '서울%' ) SELECT I.REST_ID AS REST_ID , I.REST_NAME AS REST_NAME , I.FOOD_TYPE AS FOOD_TYPE , I.FAVORITES AS FAVORITES , I.ADDRESS AS ADDRESS , ROUND(AVG(R.REVIEW_SCORE), 2) AS REVIEW_SCORE FROM REST_IN_SEOUL AS I INNER JOIN REST_REVIEW AS R ON I.REST_ID = R.REST_ID GROUP BY REST_ID ORDER BY REVIEW_SCORE DES..
✅ 코드 WITH T2 AS ( SELECT CATEGORY , MAX(PRICE) AS PRICE FROM FOOD_PRODUCT GROUP BY CATEGORY ) SELECT T1.CATEGORY , T1.PRICE , T1.PRODUCT_NAME FROM FOOD_PRODUCT AS T1 INNER JOIN T2 ON T1.CATEGORY = T2.CATEGORY AND T1.PRICE = T2.PRICE WHERE T1.CATEGORY IN ('과자', '국', '김치', '식용유') ORDER BY T1.PRICE DESC WITH문(또는 FROM절 서브쿼리)를 사용하면 쉽게 풀이가 가능합니다. 식품분류별 가장 높은 가격의 값을 가져오면 됩니다. 참고로 Lv.3 문제와 유사합니다. [SQL]프..
✅ 코드 SELECT O.ANIMAL_ID , O.NAME FROM ANIMAL_OUTS AS O LEFT JOIN ANIMAL_INS AS I ON O.ANIMAL_ID = I.ANIMAL_ID WHERE I.ANIMAL_ID IS NULL; LEFT JOIN을 수행한 풀이입니다.
✅ 코드 2개의 컬럼을 사용하여 그룹화 했습니다. SELECT USER_ID , PRODUCT_ID FROM ONLINE_SALE GROUP BY USER_ID , PRODUCT_ID HAVING COUNT(ONLINE_SALE_ID) >= 2 ORDER BY USER_ID , PRODUCT_ID DESC;
