[프로그래머스] Lv1 ~Lv.4 with, exists

LV. 1 흉부외과 또는 일반외과 의사 목록 출력하기

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

프로그래머스

SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD,'%Y-%m-%d') AS HIRE_YMD 
FROM DOCTOR 
WHERE MCDP_CD="CS" OR MCDP_CD="GS" 
ORDER BY HIRE_YMD DESC, DR_NAME ASC;

 

 

LV.2 조건에 맞는 아이템들의 가격의 총합 구하기

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

프로그래머스

SELECT SUM(PRICE) AS TOTAL_PRICE 
FROM ITEM_INFO 
WHERE RARITY="LEGEND";

 

 

 

LV. 3 대여 기록이 존재하는 자동차 리스트 구하기

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

프로그래머스

SELECT DISTINCT(CAR.CAR_ID) 
FROM CAR_RENTAL_COMPANY_CAR CAR 
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY HIST 
	ON CAR.CAR_ID = HIST.CAR_ID 
WHERE CAR.CAR_TYPE="세단" AND DATE_FORMAT(HIST.START_DATE, "%m")=10 
ORDER BY CAR.CAR_ID DESC;

다른 사람의 풀이

1. Join 을 사용하지 않고 푼 방법.

SELECT DISTINCT CAR_ID
from car_rental_company_rental_history 
where car_id in (select car_id from car_rental_company_car where car_type = '세단')
and (start_date between '2022-10-01' and '2022-10-31') 
order by car_id desc

2. EXISTS 사용

SELECT DISTINCT C.CAR_ID
FROM CAR_RENTAL_COMPANY_CAR C
WHERE EXISTS (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H WHERE C.CAR_ID = H.CAR_ID AND DATE_FORMAT(START_DATE, '%Y-%m') = '2022-10')
AND C.CAR_TYPE = '세단'
ORDER BY C.CAR_ID DESC

exists 에 관한 글

[MYSQL] 📚 서브쿼리 연산자 EXISTS 총정리 (성능 비교)

https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html

MySQL :: MySQL 8.0 Reference Manual :: 15.2.15.6 Subqueries with EXISTS or NOT EXISTS

 

 

 

 

LV.4 식품분류별 가장 비싼 식품의 정보 조회하기

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

SELECT CATEGORY, PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE PRICE IN (SELECT MAX(PRICE) 
        FROM FOOD_PRODUCT 
        GROUP BY CATEGORY) 
    AND CATEGORY IN ('과자','국','김치','식용유')
ORDER BY PRICE DESC

 

다른 사람의 풀이

with mylist as (
SELECT category, max(price) as max_price, product_name
from food_product
where category in ('과자','국','김치','식용유')
group by category, product_name
order by max_price desc) 

select *
from mylist
group by category;

MYSQL WITH

https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions

'WITH'절은 서브쿼리를 만들고 재사용 가능한 공통 테이블 표현식(Common Table Expression, CTE)을 정의하는 방법