[Programmers] String, Date 문제 풀이

 

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

 

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;

> 날짜 함수 datediff 뒤에 +1 붙는 거 조심하기!

 

 

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

 

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 date_format(start_date, '%Y-%m') = '2022-09'
ORDER BY history_id desc;

 

 

- 특정 옵션이 포함된 자동차 리스트 구하기

like와 in은 함께 사용 불가능 -> 이를 regexp_like로 사용해 해결 가능

 

SELECT car_id, car_type, daily_fee, options
FROM CAR_RENTAL_COMPANY_CAR
WHERE regexp_like(options, '네비게이션')
ORDER BY car_id desc;

 

 

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

 

SELECT u.user_id, u.nickname
    , concat(u.city, ' ', u.street_address1, ' ', u.street_address2) as '전체주소'
    , concat(left(u.tlno, 3), '-', mid(u.tlno, 4, 4), '-', right(u.tlno, 4)) as '전화번호'
FROM USED_GOODS_BOARD as b
    INNER JOIN USED_GOODS_USER as u
        ON b.WRITER_ID = u.USER_ID
GROUP BY u.USER_ID
HAVING count(*)>=3
ORDER BY u.USER_ID desc;

 

 

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

 

SELECT distinct(c.car_id)
FROM CAR_RENTAL_COMPANY_CAR as c
    inner join CAR_RENTAL_COMPANY_RENTAL_HISTORY as h
        on c.car_id = h.car_id
WHERE regexp_like(c.car_type, '세단')
    and month(start_date) = 10
ORDER BY c.car_id desc;

 

 

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

 

SELECT concat('/home/grep/src/', board_id, '/', file_id, file_name, file_ext) as file_path
FROM USED_GOODS_FILE
WHERE board_id = (SELECT board_id
    FROM USED_GOODS_BOARD
    ORDER BY views desc
    LIMIT 1)
ORDER BY file_id desc;

 

 

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

 

car_rental_company_discount_plan : 대여기간별 할인율 정보

 

1. 테이블 P를 조인해 해당 값(discount_rate)을 select 절에서 직접적으로 가져와 사용

replace(DURATION_TYPE, '일 이상', '') -> 숫자만 추출하는 방법만 고민했는데, 반대로 '문자를 없애는 방식'으로 접근

 

SELECT H.HISTORY_ID,
    ROUND((((100 - COALESCE(p.discount_rate, 0)) * C.DAILY_FEE) * (DATEDIFF(H.END_DATE, H.START_DATE) + 1)) / 100, 0) AS FEE # 할인율(%) * 일일 대여 요금
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
    INNER JOIN CAR_RENTAL_COMPANY_CAR C
        ON H.CAR_ID = C.CAR_ID
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P 
        ON C.CAR_TYPE = P.CAR_TYPE
        and replace(P.DURATION_TYPE, '일 이상', '') <= (DATEDIFF(H.END_DATE, H.START_DATE) + 1)
WHERE C.CAR_TYPE = '트럭'
GROUP BY h.history_id
ORDER BY FEE DESC, HISTORY_ID DESC;

 

* coalesce(컬럼1, 컬럼2, 컬럼3 ...) : 처음으로 NULL이 아닌 컬럼을 만나면 그 컬럼의 값 반환

coalesce 함수는 다음과 같이 내부적으로 case when을 사용해 구현한 것과 같다.

 

case
	when a is not null then a
    when b is not null then b
    else c
end

 

 

2. 테이블 P의 discount_rate 값 확인 후 백분율을 임의적으로 계산한 값을 select 절에서 사용

이렇게 한 사람들이 많은데 이러면 나중에 할인율이 바뀌었을 때 수정해야 하니까 효율이 떨어지지 않을까..

 

SELECT h.history_id
    , round(c.daily_fee * (datediff(h.end_date, h.start_date) + 1) *
    (case
        when datediff(h.end_date, h.start_date) + 1 <= 7 then 1
        when datediff(h.end_date, h.start_date) + 1 <= 30 then 0.95
        when datediff(h.end_date, h.start_date) + 1 <= 90 then 0.92
        else 0.85
     end)) as fee # 일일 대여 요금 * 대여 기간 * 할인율
FROM CAR_RENTAL_COMPANY_CAR C
    JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
        ON C.CAR_ID = H.CAR_ID
    JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
        ON C.CAR_TYPE = P.CAR_TYPE
WHERE C.CAR_TYPE = '트럭'
GROUP BY h.history_id # join 후 history_id 중복 발생
ORDER BY FEE DESC, HISTORY_ID DESC;

 

 

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

 

SELECT a.apnt_no, p.pt_name, p.pt_no, d.mcdp_cd, d.dr_name, a.apnt_ymd
FROM APPOINTMENT A
    left join PATIENT P
        on a.pt_no = p.pt_no
    left join DOCTOR D
        on a.mddr_id = d.dr_id
WHERE date_format(a.apnt_ymd, '%Y-%m-%d') = '2022-04-13' 
    and a.apnt_cncl_yn = 'N' 
    and a.mcdp_cd = 'CS'
ORDER BY a.apnt_ymd;

 

 

- 조건별로 분류하여 주문상태 출력하기

 

SELECT order_id, product_id, date_format(out_date, '%Y-%m-%d') as out_date,
	(case when date_format(out_date, '%m-%d') <= '05-01' then '출고완료'
        when date_format(out_date, '%m-%d') > '05-01' then '출고대기'
        else '출고미정'
	end) as '출고여부'
FROM FOOD_ORDER
ORDER BY order_id;

 

 

- 조건에 부합하는 중고거래 상태 조회하기

 

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, 
    (CASE WHEN STATUS = 'SALE' then '판매중'
        WHEN STATUS = 'RESERVED' then '예약중'
        WHEN STATUS = 'DONE' then '거래완료'
    END) as STATUS
FROM USED_GOODS_BOARD
WHERE DATE_FORMAT(CREATED_DATE, '%Y-%m-%d') = '2022-10-05'
ORDER BY BOARD_ID desc;

 

 

- 루시와 엘라 찾기

 

SELECT animal_id, name, sex_upon_intake
FROM animal_ins
WHERE name in ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty');

 

 

- 이름에 el이 들어가는 동물 찾기

 

SELECT animal_id, name
FROM animal_ins
WHERE name like '%el%' and animal_type = 'Dog'
ORDER BY name;

https://enterone.tistory.com/311

[MySQL][TIP] LIKE 문에서 대소문자 구분하기

 

 

- 중성화 여부 파악하기

 

SELECT animal_id
    , name
 -- , if(sex_upon_intake regexp 'Neutered|Spayed', 'O', 'X') as 중성화
    , if(sex_upon_intake like '%Neutered%' or sex_upon_intake like '%Spayed%', 'O', 'X') as 중성화
FROM animal_ins
ORDER BY animal_id;

https://velog.io/@gillog/MySQL-REGEXPRegular-Expression%EC%A0%95%EA%B7%9C-%ED%91%9C%ED%98%84%EC%8B%9D

[MySQL] REGEXP(Regular Expression(정규 표현식))

 

 

- 오랜 기간 보호한 동물(2)

 

SELECT i.animal_id, i.name
FROM ANIMAL_INS as i
    left join ANIMAL_OUTS as o
        on i.animal_id = o.animal_id
WHERE o.datetime is not null
ORDER BY datediff(o.datetime, i.datetime) desc
LIMIT 2;

 

 

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

 

SELECT left(product_code, 2) as category, count(product_code)
FROM product
GROUP BY category
ORDER BY category;