(Programmers) SQL 풀이 (7)

✏️ Programmers MySQL 정답률 70~72% 전체 풀이

1️⃣ 진료과별 총 예약 횟수 출력하기 (https://school.programmers.co.kr/learn/courses/30/lessons/132202)

SELECT MCDP_CD AS "진료과코드", COUNT(APNT_NO) AS "5월예약건수"
FROM APPOINTMENT
WHERE MONTH(APNT_YMD) = 5
GROUP BY MCDP_CD
ORDER BY 2, 1; # ORDER BY 5월예약건수 ASC, 진료과코드 ASC; (SELECT index 활용)

2️⃣ 조건별로 분류하여 주문상태 출력하기 (https://school.programmers.co.kr/learn/courses/30/lessons/131113)

SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE,"%Y-%m-%d"), (
    # 비교해야 할 조건이 여러개일 때는 CASE WHEN 문법 사용
    CASE 
    WHEN (OUT_DATE IS NULL) THEN '출고미정'
    WHEN (DATEDIFF(OUT_DATE, '2022-05-01') <= 0) THEN '출고완료'
    ELSE '출고대기'
    END
) AS "출고여부"
FROM FOOD_ORDER
ORDER BY ORDER_ID;

# CASE 문법
SELECT column-names,
(
 CASE column-name
 WHEN condition THEN order
 WHEN condition THEN order
 ElSE order
 END
) AS display-name # AS 생략 가능
FROM table-name;

시간 차를 확인 하는 다른 문제 : https://school.programmers.co.kr/learn/courses/30/lessons/59043

 

3️⃣ 우유와 요거트가 담긴 장바구니 (https://school.programmers.co.kr/learn/courses/30/lessons/62284)

SELECT CART_ID
FROM (
    SELECT ID, CART_ID, NAME
    FROM CART_PRODUCTS
    WHERE NAME IN ('Yogurt', 'Milk')
) AS SUB
GROUP BY CART_ID
HAVING COUNT(DISTINCT NAME) >= 2
ORDER BY CART_ID;