자동차 대여 기록에서 장기/단기 대여 구분하기

• 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력

[틀린 코드]

SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, '%Y-%m-%d') as START_DATE, 
    DATE_FORMAT(END_DATE, '%Y-%m-%d') as END_DATE,
    IF(END_DATE - START_DATE >=30, '장기 대여', '단기 대여') as RENT_TYPE
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where YEAR(START_DATE) = 2022 and MONTH(START_DATE) = 09
order by HISTORY_ID desc

• 대여 기간을 IF(END_DATE - START_DATE >=30, '장기 대여', '단기 대여') 로 표현하였다.
  하지만 그냥 뺄셈으로 하면 문자 간의 뺄셈이 되는 것 같다.

• DATE의 기간을 알 수 있는 함수로 DATEDIFF가 있다.

SELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, '%Y-%m-%d') as START_DATE, 
    DATE_FORMAT(END_DATE, '%Y-%m-%d') as END_DATE,
    IF(DATEDIFF(END_DATE, START_DATE)+1 >=30, '장기 대여', '단기 대여') as RENT_TYPE
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where YEAR(START_DATE) = 2022 and MONTH(START_DATE) = 9
order by HISTORY_ID desc

• DATEDIFF(END_DATE, START_DATE)+1
  • 1을 더하는 이유는?
  • 대여 시작일과 반납일이 같아도 대여 기간은 하루로 치기 때문에

✔️ DATE 함수

• DATEDIFF(date1, date2): date1 - date2
  • 순서에 주의할 것!
  • 나중, 먼저로 작성할 것
• CURTIME() / now()

✔️ String

• CONCAT(expression1, expression2, expression3,...)
  • 인자들을 이어주는 함수
• STRCMP(string1, string2): 두 문자열을 비교하는 함수
• UPPER()
• LOWER()
• SUBSTR(string, start, length)
  • 배열과 달리, 첫 글자는 1로 센다
  • start 부터 length만큼 반환
• LEFT(string, number_of_chars)
  • 문자열의 왼쪽부터 n개의 문자를 가져옴

---

조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

SELECT concat('/home/grep/src/', F.BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) as FILE_PATH
from USED_GOODS_FILE F
join USED_GOODS_BOARD B
on F.BOARD_ID = B.BOARD_ID
where F.BOARD_ID = (select BOARD_ID 
                    from USED_GOODS_BOARD 
                    order by VIEWS desc 
                    limit 1)
order by FILE_ID desc

• CONCAT()을 사용하여 String을 생성

---

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

SELECT substr(PRODUCT_CODE, 1,2) as CATEGORY, count(*) as PRODUCTS
from PRODUCT
group by CATEGORY
order by CATEGORY

• substr() 사용
• substr() 대신에 LEFT()를 사용해도 된다. -> LEFT(PRODUCT_CODE, 2)

---

조건에 맞는 사용자 정보 조회하기

SELECT U.USER_ID, U.NICKNAME, concat(CITY, ' ', STREET_ADDRESS1, ' ', STREET_ADDRESS2) as 전체주소, 
concat(substr(TLNO,1,3), '-', substr(TLNO,4,4), '-', substr(TLNO,8,4)) as 전화번호
from USED_GOODS_BOARD B
join USED_GOODS_USER U on U.USER_ID = B.WRITER_ID
group by WRITER_ID
having count(*) >=3
order by U.USER_ID desc;

---

⭐️ 자동차 대여 기록 별 대여 금액 구하기(LEVEL 4)

• 문제가 복잡했다!
• 여러 join 들이 있다보니, 하나의 select 절에 다 넣기에는 오류를 잡기 힘들어 보였다 -> with 절 사용

✔️ with 절

WITH 가상테이블명 AS
(
    SELECT 쿼리
)

내가 틀린 이유

• 출력되는 값은 동일한데, 계속 틀린 이유는 outer join 때문이다.
• with 절에서 inner join을 사용하고, 다시 select 절에서 inner join을 사용했는데 outer join을 사용했어야 했다.
  => 답이 맞는 것 같은데 안 된다면 inner join / outer join 문제인지도 고려해보자!

수정한 코드

with A as (select HISTORY_ID, H.CAR_ID, START_DATE, END_DATE, CAR_TYPE, DAILY_FEE, 
           (CASE WHEN DATEDIFF(END_DATE, START_DATE)+1 >= 90 then "90일 이상"
                    when DATEDIFF(END_DATE, START_DATE)+1 >= 30 then "30일 이상"
                    when DATEDIFF(END_DATE, START_DATE)+1 >= 7 then "7일 이상"
                    else "NULL" end) as duration 
from CAR_RENTAL_COMPANY_RENTAL_HISTORY H
join CAR_RENTAL_COMPANY_CAR C on H.CAR_ID = C.CAR_ID
where C.CAR_TYPE = '트럭')


SELECT A.HISTORY_ID, round(DAILY_FEE * (DATEDIFF(END_DATE, START_DATE)+1)* (100 - IFNULL(DISCOUNT_RATE , 0)) / 100) as FEE
from A
left join CAR_RENTAL_COMPANY_DISCOUNT_PLAN P on (P.CAR_TYPE = A.CAR_TYPE and P.DURATION_TYPE = A.duration)
order by FEE desc, HISTORY_ID desc

https://hellobrocolli.tistory.com/27
참고한 블로그