SQL LV2~4 - programmers

1. 3월에 태어난 여성 회원 목록 출력하기

SELECT  MEMBER_ID, MEMBER_NAME, GENDER , 
        date_format(DATE_OF_BIRTH, "%Y-%m-%d") AS DATE_OF_BIRTH
FROM    MEMBER_PROFILE
WHERE   TLNO IS NOT NULL &&
        GENDER = 'W' &&
        date_format(DATE_OF_BIRTH, '%m') = 3
ORDER BY MEMBER_ID ASC

• date_format

---

동명 동물 수 찾기

SELECT  NAME, COUNT(*) AS COUNT
FROM    ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) >= 2
ORDER BY NAME

• having
  집계함수를 사용할 때

---

row 수 구하기

SELECT  COUNT(ANIMAL_ID)
FROM    ANIMAL_INS;

• null이 될 수 없는 primary key로 세도록 한다

---

중복 제거하기

SELECT  COUNT(*)
FROM    (
    SELECT NAME
    FROM ANIMAL_INS
    GROUP BY NAME
) AS A
WHERE NAME IS NOT NULL

• NOT NULL을 꼭 써줘야 함!

  ---

가격이 제일 비싼 식품의 정보 출력하기

SELECT  *
FROM    FOOD_PRODUCT
ORDER BY PRICE DESC
LIMIT 1;

# 서브쿼리로
SELECT * 
FROM FOOD_PRODUCT 
WHERE PRICE = (SELECT MAX(F.PRICE) from FOOD_PRODUCT F)

---

입양 시각 구하기(1)

SELECT  HOUR(DATETIME) AS HOUR, COUNT(DATETIME) AS COUNT
FROM    ANIMAL_OUTS
WHERE   HOUR(DATETIME) BETWEEN 9 AND 19
GROUP BY HOUR
ORDER BY HOUR

---

카테고리 별 상품 개수 구하기

SELECT  SUBSTRING(PRODUCT_CODE,1,2) AS CATEGORY, 			
		COUNT(PRODUCT_ID) AS PRODUCTS
FROM    PRODUCT
GROUP BY CATEGORY
ORDER BY CATEGORY;

• SUBSTRING(컬럼 이름, n번째 글자 부터, m 번째 글자 까지)
  • 1 부터 시작

---

진료과별 총 예약 횟수 출력하기

SELECT  MCDP_CD AS '진료과 코드',
        COUNT(APNT_NO) AS '5월예약건수'
FROM    APPOINTMENT
WHERE   APNT_YMD LIKE '2022-05%'
GROUP BY MCDP_CD
ORDER BY 2, 1;

• GROUP BY '진료과 코드'로 하면 안됨..!

---

가격대 별 상품 개수 구하기

SELECT  (PRICE - PRICE%10000) AS PRICE_GROUP, 
		COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT 
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

• case를 일일히 적지 않고 만으로 나눈 나머지를 가격에서 빼면 된다.!

---

동일한 회원이 동일한 상품을 재구매

SELECT  USER_ID, PRODUCT_ID
FROM    ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING  COUNT (USER_ID) > 1
ORDER BY 1, 2 DESC

• GROUP BY USER_ID, PRODUCT_ID
  두개 컬럼을 기준으로 묶는다

---

자동차 평균 대여 기간 구하기

SELECT  CAR_ID, 
        ROUND(AVG(DATEDIFF(END_DATE,START_DATE)+1),1) AS AVERAGE_DURATION
FROM    CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING   AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

• HAVING이 GROUP BY 다음에

---

오랜 기간 보호한 동물(1)

SELECT  I.NAME, I.DATETIME
FROM    ANIMAL_INS AS I
LEFT JOIN ANIMAL_OUTS AS O
ON  I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.DATETIME IS NULL
ORDER BY DATETIME 
LIMIT 3;

• LEFT JOIN
  입양가지 못한 동물들은 아웃 데이트가 null 인 것을 이용

---

보호소에서 중성화한 동물

SELECT  I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME
FROM    ANIMAL_INS AS I
JOIN    ANIMAL_OUTS AS O
ON      I.ANIMAL_ID = O.ANIMAL_ID
WHERE   I.SEX_UPON_INTAKE LIKE 'INTACT%'
        && O.SEX_UPON_OUTCOME NOT LIKE 'INTACT%'
ORDER BY I.ANIMAL_ID

# LOCATE 사용시
ELECT  I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME
FROM    ANIMAL_INS AS I
JOIN    ANIMAL_OUTS AS O
ON      I.ANIMAL_ID = O.ANIMAL_ID
WHERE LOCATE('intact', I.SEX_UPON_INTAKE) >= 1 
      && LOCATE('intact', O.SEX_UPON_OUTCOME) = 0

• in, not in
• LOCATE(substring, string, start)
  • >= 1 첫번째 이상부터 substring이 존재
  • = 0 없음
• LOCATE(substring, column-name)
  시작 위치를 리턴

---

카테고리 별 도서 판매량 집계하기

SELECT  B.CATEGORY, SUM(S.SALES) AS TOTAL_SALES
FROM    BOOK_SALES AS S
JOIN    BOOK AS B
ON      S.BOOK_ID = B.BOOK_ID
WHERE   S.SALES_DATE LIKE '2022-01%'
GROUP BY B.CATEGORY
ORDER BY B.CATEGORY
;

• 총 판매량이므로 sum으로 집계해야함!

---

조건에 맞는 사용자와 총 거래금액 조회하기

WITH B AS (
    SELECT  WRITER_ID
            , SUM(PRICE) AS TOTAL_SALES
    FROM    USED_GOODS_BOARD
    WHERE   STATUS IN ('DONE')
    GROUP BY WRITER_ID 
)

SELECT  U.USER_ID, U.NICKNAME, B.TOTAL_SALES
FROM    B
JOIN    USED_GOODS_USER AS U
ON      B.WRITER_ID = U.USER_ID
WHERE   TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES;

• WITH TABLE_NAME AS ()
  로 status로 우선 골라서 테이블을 만들어 줬다. 왜냐면 집계함수 카운트 할 때 having 으로 조건을 걸어주는걸 까먹음

SELECT  U.USER_ID, U.NICKNAME, SUM(B.PRICE)TOTAL_SALES
FROM    USED_GOODS_BOARD AS B
JOIN    USED_GOODS_USER AS U
ON      B.WRITER_ID = U.USER_ID
WHERE   B.STATUS = 'DONE'
GROUP BY U.USER_ID
HAVING  TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES;

• GROUP BY는 where 다음에 옴

---

🚨즐겨찾기가 가장 많은 식당 정보 출력하기

식품분류별 가장 비싼 식품의 정보 조회하기와 비슷

# 다중 조건 IN (서브쿼리)
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN (SELECT FOOD_TYPE, MAX(FAVORITES)
    FROM REST_INFO
    GROUP BY FOOD_TYPE
    )
ORDER BY FOOD_TYPE DESC
;

# JOIN
WITH FAV AS (
    SELECT 	FOOD_TYPE, MAX(FAVORITES) AS FAVORITES
    FROM 	REST_INFO 
    GROUP BY FOOD_TYPE
) 

SELECT  R.FOOD_TYPE, R.REST_ID, R.REST_NAME, R.FAVORITES
FROM    REST_INFO AS R
JOIN    FAV
ON  	FAV.FOOD_TYPE = R.FOOD_TYPE 
    	&& FAV.FAVORITES = R.FAVORITES
ORDER BY FOOD_TYPE DESC;

• 테이블을 이중으로 만들어 비교를 해야 하는 이유
  max값은 제대로 가져오는데 다른 식당 정보(REST_NAME)를 다른 row에서 가져올 수 있음!

  FOOD_TYPE	REST_ID	REST_NAME	FAVORITES
  한식	00001	은돼지식당	734
  중식	00015	만정		 20
  일식	00002	하이가쯔네	230
  양식	00003	따띠따띠뜨	102
  분식	00008	애플우스	151
  -> 한 겹?으로 돌린경우 일식의 식당 정보가 틀림
  
  FOOD_TYPE	REST_ID	REST_NAME	FAVORITES
  한식	00001	은돼지식당	734
  중식	00015	만정	      20
  일식	00004	스시사카우스	230
  양식	00003	따띠따띠뜨	102
  분식	00008	애플우스	151
  -> 서브쿼리로 돌린경우 알맞은 식당 정보

• table1: 음식 종류 당 최다 즐겨찾기
  table2: 모든 정보가 다 담긴 테이블

---

SELECT  R.REST_ID,	I.REST_NAME,	I.FOOD_TYPE,	I.FAVORITES,	
        I.ADDRESS, ROUND(AVG(R.REVIEW_SCORE),2) AS SCORE
FROM    REST_INFO AS I
JOIN    REST_REVIEW AS R
ON      I.REST_ID = R.REST_ID
WHERE   ADDRESS LIKE '서울%'
GROUP BY I.REST_ID,	I.REST_NAME,	I.FOOD_TYPE,	I.FAVORITES,	
        I.ADDRESS
ORDER BY SCORE DESC, FAVORITES DESC ;

• 계속 틀리길래 왜이럴까 하고 '%서울%' 에서 '서울%'로 바꿨더니 맞았다. 결과는 어떻게 하든 똑같았는데🤔 대한민국으로 시작할 수도 있지 않나유

• 3번째에서 반올림하면 2번째 자리까지 표시된다. 그래서 ROUND(,2) 로 해야함

• LIKE Operator Description

  • % 문자 혹은 숫자

  • _ 자리값

    'a%'	start with "a"
    '%a'	end with "a"
    '%or%'	"or" in any position
    '_r%'	"r" in the second position
    'a_%'	start with "a" and are at least 2 characters in length
    'a__%'	start with "a" and are at least 3 characters in length
    'a%o'	start with "a" and ends with "o"

---

우유와 요거트가 담긴 장바구니

WITH MILK AS (
    SELECT  CART_ID
    FROM    CART_PRODUCTS
    WHERE   NAME = 'Milk'
)

SELECT  DISTINCT YOG.CART_ID
FROM    CART_PRODUCTS AS YOG
JOIN    MILK
ON      MILK.CART_ID = YOG.CART_ID
WHERE   NAME = 'YOGURT'
ORDER BY CART_ID

# 합쳐서 이렇게 쓸 수도 있다
SELECT DISTINCT YOG.CART_ID
FROM    CART_PRODUCTS MILK
JOIN    CART_PRODUCTS YOG
ON      A.CART_ID = B.CART_ID
WHERE   MILK.NAME = 'Milk' 
        && YOG.NAME = 'Yogurt'
ORDER BY MILK.CART_ID;

• DISTINCT 를 써줘야 중복되는 아이디 없이 나옴

---

취소되지 않은 진료 예약 조회하기

WITH AP AS 
(
    SELECT  A.APNT_NO, P.PT_NAME, P.PT_NO, A.MCDP_CD, A.APNT_YMD, A.MDDR_ID
    FROM    APPOINTMENT AS A
    INNER JOIN    PATIENT AS P
    ON      A.PT_NO = P.PT_NO
    WHERE   A.APNT_YMD LIKE '2022-04-13%'
            && A.APNT_CNCL_YN = 'N'
)

SELECT  AP.APNT_NO, AP.PT_NAME, AP.PT_NO, AP.MCDP_CD,
        D.DR_NAME, AP.APNT_YMD
FROM    AP
JOIN    DOCTOR AS D
ON      D.DR_ID = AP.MDDR_ID
ORDER BY APNT_YMD;

• 중복되는 컬럼 이름이 있을 때 *로 모두 끌어 오지 않고 컬럼명대로 명시해서 골라준다

---

🚨 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

SELECT  CAR_ID,
        CASE 
            WHEN MAX('2022-10-16' BETWEEN START_DATE AND END_DATE)
            THEN '대여중'
            ELSE '대여 가능'
        END AVAILABILITY
FROM    CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY 1
ORDER BY 1 DESC;


# 서브쿼리 이용
WITH CAR AS (
    SELECT  CAR_ID,
            CASE
            WHEN    START_DATE <= '2022-10-16' AND '2022-10-16' <= END_DATE 
            THEN 1 ELSE 0
            END A
    FROM    CAR_RENTAL_COMPANY_RENTAL_HISTORY
    GROUP BY CAR_ID, A
)
SELECT  H.CAR_ID,
        CASE 
            WHEN MAX(CAR.A) = 1 
            THEN '대여중' ELSE '대여 가능' 
        END as AVAILABILITY
FROM    CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
JOIN    CAR
ON      CAR.CAR_ID = H.CAR_ID
GROUP BY 1
ORDER BY 1 DESC;

• MAX('2022-10-16' BETWEEN START_DATE AND END_DATE)
  해당되면 1, 아니면 0 을 돌려준다. 1 -> then, 0 -> else
  서브쿼리를 이용할 필요가 없음

---

SELECT  A.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY,
        SUM(S.SALES * B.PRICE) AS TOTAL_SALES
FROM    BOOK_SALES AS S
    JOIN BOOK AS B ON S.book_id = B.book_id
    JOIN AUTHOR A ON B.author_id = A.author_id
WHERE SALES_DATE LIKE '2022-01%'
GROUP BY 1, 3
ORDER BY 1, 3 DESC

• SUM(S.SALES) * B.PRICE AS TOTAL_SALES ❌

  AUTHOR_ID	AUTHOR_NAME	CATEGORY	TOTAL_SALES
  1	홍길동	인문	561000
  1	홍길동	경제	1212000
  2	김영호	소설	450000
  3	김수진	생활	817000
  3	김수진	기술	1661000

• SUM(S.SALES * B.PRICE) AS TOTAL_SALES ⭕️

      AUTHOR_ID	AUTHOR_NAME	CATEGORY	TOTAL_SALES
      1	홍길동	인문	561000
      1	홍길동	경제	1107000
      2	김영호	소설	450000
      3	김수진	생활	817000
      3	김수진	기술	1523000

  왜 자꾸 틀리는지 답답했는데 괄호가 문제였다..
  기호로 하면 대충 (a+b) * c
  ac + bc 일텐데 값이 다르게 나온다..!

---

대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

SELECT
    MONTH(RT1.START_DATE) AS 'MONTH',
    RT1.CAR_ID,
    COUNT(RT2.HISTORY_ID) AS 'RECORDS'
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY RT1
    JOIN (
        SELECT *
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY

        WHERE YEAR(START_DATE) = '2022'
            AND MONTH(START_DATE) BETWEEN 8 AND 10

        GROUP BY CAR_ID
        HAVING COUNT(HISTORY_ID) >= 5
    ) RT2 ON RT1.CAR_ID = RT2.CAR_ID

WHERE MONTH(RT1.START_DATE) BETWEEN 8 AND 10
GROUP BY MONTH, RT1.CAR_ID
HAVING RECORDS > 0
ORDER BY MONTH ASC, RT1.CAR_ID DESC

• WHERE MONTH(RT1.START_DATE) BETWEEN 8 AND 10
  를 두번 적용 해줘야 했음. 눈으로 확인하긴 했지만 결과 값은 같았는데.. 아직 이유는 모르겠음

---

그룹별 조건에 맞는 식당 목록 출력하기

WITH M AS ( # 최대 댓글 회원들을 알아내기 위한 쿼리
    SELECT DISTINCT MEMBER_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    HAVING COUNT(MEMBER_ID) >= (
    	# 최고 빈도수를 알아내기 위한 쿼리
        SELECT  COUNT(MEMBER_ID) AS CNT
        FROM    REST_REVIEW
        GROUP BY MEMBER_ID
        ORDER BY CNT DESC
        LIMIT 1
    )
)

SELECT  P.MEMBER_NAME, R.REVIEW_TEXT, 
        DATE_FORMAT(R.REVIEW_DATE,'%Y-%m-%d') AS REVIEW_DATE
FROM    MEMBER_PROFILE AS P
NATURAL JOIN REST_REVIEW AS R
NATURAL JOIN    M 
ORDER BY 3, 2 ;

• 제일 여러번 쓴 아이디의 횟수를 보니 3번 이었지만 3번 글 쓴 아이디들이 여러개인 경우

---

입양 시각 구하기(2)

<사전 연습>

UNION

SELECT column_name(s) 
FROM table1
WHERE condition1

UNION

SELECT column_name(s) 
FROM table2
WHERE condition2;

• 두개 이상의 쿼리 셋을 통합할 때 이용한다
  • 컬럼의 수가 동일해야 한다
  • 컬럼의 데이터 타입이 같아야 한다
  • 통합할 컬럼들을 같은 순서로 배치시켜야 한다
• UNION ALL 중복값 허용

RECURSIVE

WITH RECURSIVE FIVE AS(
    SELECT 1 as NUM 
    # 셀렉트 문으로 초기값과 컬럼이름 명시
 
    UNION 
 
    SELECT NUM+1 
    FROM FIVE 
    WHERE NUM < 5;
) # 1<= x <= 5

SELECT 	* 
FROM	FIVE;
# 내림차수로 하고 싶다면 order by desc

NUM
1
2
3
4
5

• 감소 --

WITH RECURSIVE THREE AS(
    SELECT 5 as NUM
 
    UNION 
 
    SELECT 	NUM-1 
    FROM	THREE 
    WHERE Mon > 1;
) # 3>= x >= 1

SELECT 	* 
FROM	THREE;

NUM
3
2
1

본편

WITH RECURSIVE H AS(
   SELECT 0 as HOURS 

   UNION

   SELECT HOURS+1 
   FROM H 
   WHERE HOURS < 23
) 

SELECT  HOURS, 
		CASE
           WHEN O.COUNT IS NULL
           THEN 0
           ELSE O.COUNT
       	END COUNT
FROM    H
LEFT JOIN (
   SELECT  HOUR(DATETIME) AS HOUR, COUNT(ANIMAL_ID) AS COUNT
   FROM    ANIMAL_OUTS
   GROUP BY HOUR
) AS O
ON      H.HOURS = O.HOUR

• 시간목록을 왼쪽에 두고 left join을 하자

---

오프라인/온라인 판매 데이터 통합하기

(
    SELECT  DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, 
            PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
    FROM    OFFLINE_SALE 
    WHERE   MONTH(SALES_DATE) = 3
)

UNION

(
    SELECT  DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, 
            PRODUCT_ID, USER_ID, SALES_AMOUNT
    FROM    ONLINE_SALE
    WHERE   MONTH(SALES_DATE) = 3
)

ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;

• NULL AS USER_ID
  오프라인 판매에는 유저아이디가 없으므로 null값을 user_id컬럼에 넣어주기
• UNION
  중복되는 row가 없으므로 (user_id 가 오프라인 판매에선 없는 이유로) union으로 괜찮다.

---

🚨 상품을 구매한 회원 비율 구하기

WITH M21 AS (
    SELECT  *
    FROM    USER_INFO
    WHERE   YEAR(JOINED) = 2021
)

SELECT YEAR(SALES_DATE) AS YEAR, 
       MONTH(SALES_DATE) AS MONTH,
       COUNT(DISTINCT USER_ID) AS PUCHASED_USERS,
       ROUND( COUNT(DISTINCT USER_ID) 
             / (SELECT COUNT(*) FROM M21), 1) AS RATIO
FROM ONLINE_SALE
JOIN M21 USING(USER_ID)
GROUP BY 2
ORDER BY 2;

  YEAR	MONTH	PUCHASED_USERS	M21	RATIO
  2022	1	47	158	0.3
  2022	2	40	158	0.3
  2022	3	6	158	0.0

• SELECT 안에 SELECT를 넣을 수 있음
• 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)를 이해하기 어려웠다..
  • M21에서 구한 2021년에 가입한 회원들의 정보를 조인해서 그 테이블 안에서 값을 구하라는 말이었음.
  • 내가 이해한거 -> 2021년에 가입한 회원들이 2022년 구매자들 중 몇퍼센트를 차지 하는지.. 독해를 잘하자..!

---

특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

CONDITIONS

1. 테이블 세개
   CAR_RENTAL_COMPANY_CAR, CAR_RENTAL_COMPANY_RENTAL_HISTORY,
   CAR_RENTAL_COMPANY_DISCOUNT_PLAN

2. 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중

3. 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고
   -> NOT IN 11월 1일 이후 부터 빌리기 시작하거나 OR (11월 1일 이전에 빌려서) 11월 1일 이후에 반납하거나

4. 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서
   -> HAVING 으로 조건을 걸어준다. 집계함수를 사용하고 있기 때문

5. SELECT
   자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE)

6. ORDER BY

   • 결과는 대여 금액을 기준으로 내림차순 정렬하고,
   • 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬,
   • 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

SELECT  N.CAR_ID, P.CAR_TYPE
        , ROUND(C.DAILY_FEE * 30 * (100 - P.DISCOUNT_RATE)/100,0) AS FEE
FROM    CAR_RENTAL_COMPANY_CAR AS C
JOIN    (
    SELECT  DISTINCT CAR_ID
    FROM    CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE   CAR_ID NOT IN (
        SELECT  CAR_ID
        FROM    CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE   START_DATE >= '2022-11-01'
                || END_DATE >= '2022-11-01'
    )
) AS N USING (CAR_ID)
JOIN    (
    SELECT  *
    FROM    CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE   DURATION_TYPE LIKE '30%'
) AS P USING (CAR_TYPE)
WHERE   CAR_TYPE IN ('세단','SUV' )
HAVING FEE >= 500000 AND FEE < 2000000 
ORDER BY 3 DESC, 2, 1 DESC;

CAR_ID	CAR_TYPE	FEE
3	세단	1518000
23	세단	1380000

• 기간 조정 할 때 in을 쓸지 not in을 써야할지 잘 생각하기!

---

자동차 대여 기록 별 대여 금액 구하기

WITH T AS (
    SELECT  *, COALESCE(DR, 0) AS DDR
    FROM    CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
    NATURAL JOIN (
        SELECT  *
                , DATEDIFF(END_DATE, START_DATE)+1 AS DAYS
                , CASE
                    WHEN DATEDIFF(END_DATE, START_DATE)+1 < 7 THEN 0
                    WHEN DATEDIFF(END_DATE, START_DATE)+1 < 30 THEN 1
                    WHEN DATEDIFF(END_DATE, START_DATE)+1 < 90 THEN 2
                    ELSE 3
                END GR
        FROM    CAR_RENTAL_COMPANY_RENTAL_HISTORY
        NATURAL JOIN CAR_RENTAL_COMPANY_CAR AS C
        WHERE   C.CAR_TYPE = '트럭'
    ) AS C 
    LEFT JOIN    (
        SELECT  DISCOUNT_RATE AS DR
                , CASE
                WHEN DURATION_TYPE LIKE '7%' THEN 1
                WHEN DURATION_TYPE LIKE '3%' THEN 2
                WHEN DURATION_TYPE LIKE '9%' THEN 3
                END GR
        FROM    CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE   CAR_TYPE = '트럭'
    ) AS P USING(GR)
)

SELECT  HISTORY_ID
        , ROUND(DAYS * DAILY_FEE * (100 - DDR)/100, 0) AS FEE
FROM T
ORDER BY 2 DESC, 1 DESC
;

• 대여일수에 맞는 할인가를 적용하기 위해서 GR칼럼을 두테이블에 모두 만들어서 조인했다
• 그런데 아무리봐도 효율성이 별로임

다음은 다른사람의 코드인데 셀렉트문 안에서 전처리하는 방법을 볼 수 있어서 가져왔다. 내 코드가 커진게 셀렉트 전처리 방법이 익숙하지 않아서 인것 같다.

SELECT  
HISTORY_ID,
FLOOR( 
	DAILY_FEE 
    * (DATEDIFF(END_DATE, START_DATE)+1) 
    * (100 - COALESCE(
    	(
        	SELECT 	MAX(DISCOUNT_RATE)
            FROM    CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
            WHERE   (
            	DATEDIFF(END_DATE, START_DATE)+1) 
              	>= 
                CAST(REPLACE(DURATION_TYPE, '일 이상', '') AS SIGNED
            ) 
                 && CAR_TYPE = '트럭' 
              ), 0)
          )
      / 100
) AS FEE
FROM    CAR_RENTAL_COMPANY_CAR R
JOIN 	CAR_RENTAL_COMPANY_RENTAL_HISTORY H 
USING 	(CAR_ID)
WHERE 	CAR_TYPE = '트럭'
ORDER BY 2 DESC, 1 DESC;

• floor의 사용으로 round(컬럼이름,0) 를 사용 할 필요가 없어짐
• 컬럼을 사용할 때 테이블 이름을 명시 하지 않아도 됨..!
• cast
  • CAST(expression AS datatype(length))
  • 숫자만 남겨서 할인율 적용을 골라냄.
    • 만약 5일 대여했으면 7일 이상의 7보다 작기 때문에 0 이됨
    • 8일 대여시 7일보다 크기 때문에 7일 이상의 할인율은 남지만 그외의 할인율은 셀렉되지 않게 된다.
  • max(discount_rate)를 골라낸다
• replace 문자열의 서브스트링 교체