[MySQL] WITH 구문

매출 탑 5 국가를 추출하는 쿼리를 작성해보자

 

TABLE 생성해서 풀기

1. 국가 매출액 등수 매기기

SELECT COUNTRY
, SALES
, DENSE_RANK() OVER(ORDER BY SALES DESC) RNK -- 매출액 기준으로 내림차순 한 뒤 등수 매기겠다
FROM CLASSICMODELS.STAT;

 

2. 테이블 생성

CREATE TABLE CLASSICMOEDELS.STAT_RNK AS
SELECT COUNTRY
, SALES
, DENSE_RANK() OVER(ORDER BY SALED DESC) RNK
FROM CLASSICMODELS.STAT;

 

3. 상위 5개 국가

SELECT *
FROM CLASSICMODELS.STAT_RNK
WHERE RNK BETWEEN 1 AND 5; -- LIMIT사용도 가능

---

SUBQUERY

1. 국가별 매출액 추출하기

SELECT C.COUNTRY
, SUM(PRICEEACH*QAUNTITYORDERED) SALES
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAIL B
ON A.ORDERNUMBER = B.ORDERNUMBER
LEFT JOIN CLASSICMODELS.CUSTOMER C
ON A.CUSTOMERNUMBER = C.CUSTOMERNUMBER
GROUP BY 1

 

2. 국가 매출액 등수 매기기

SELECT COUNTRY
, SALES
, DENSE_RANK() OVER(ORDER BY SALES DESC) RNK
FROM
(SELECT C.COUNTRY
, SUM(PRICEEACH*QAUNTITYORDERED) SALES
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAIL B
ON A.ORDERNUMBER = B.ORDERNUMBER
LEFT JOIN CLASSICMODELS.CUSTOMER C
ON A.CUSTOMERNUMBER = C.CUSTOMERNUMBER
GROUP BY 1) A

*SUBQUERY 사용 시, () A ← 네임 지정해두기

 

3. 매출 탑 5 국가 가려내기

SELECT *
FROM
(SELECT COUNTRY
, SALES
, DENSE_RANK() OVER(ORDER BY SALES DESC) RNK
FROM
(SELECT C.COUNTRY
, SUM(PRICEEACH*QAUNTITYORDERED) SALES
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAIL B
ON A.ORDERNUMBER = B.ORDERNUMBER
LEFT JOIN CLASSICMODELS.CUSTOMER C
ON A.CUSTOMERNUMBER = C.CUSTOMERNUMBER
GROUP BY 1) A) A
WHERE RNK <= 5;

---

WITH

: 이름이 부여된 서브쿼리, 테이블처럼 쓰기 가능

 

 

1. 국가별 매출액 추출하기

SELECT C.COUNTRY
, SUM(PRICEEACH*QUANTITYORDERED) SALES
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER = B.ORDERNUMBER
LEFT JOIN CLASSICMODELS.CUSTOMERS C
ON A.CUSTOMERNUMBER = C.CUSTOMERNUMBER
GROUP BY 1

 

2. 'STAT'(국가별 매출액) WITH구문 만들기

WITH STAT AS (
SELECT C.COUNTRY
, SUM(PRICEEACH*QUANTITYORDERED) SALES
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER = B.ORDERNUMBER
LEFT JOIN CLASSICMODELS.CUSTOMERS C
ON A.CUSTOMERNUMBER = C.CUSTOMERNUMBER
GROUP BY 1)

 

3. 'STAT' WITH 구문에서 국가 매출액 등수 매기기

SELECT COUNTRY
, SALES
, DENSE_RANK() OVER(ORDER BY SALES DESC) RNK
FROM STAT

 

4. 'STAT_RNK'(국가 매출액 등수) WITH구문 만들기

WITH STAT_RNK AS (
SELECT COUNTRY
, SALES
, DENSE_RANK() OVER(ORDER BY SALES DESC) RNK
FROM STAT)

 

5. WITH구문 통해 매출 탑 5 국가 추출하기

SELECT *
FROM STAT_RNK
WHERE RNK <= 5;