String && date 모음

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

- case, date_format, 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,
case when datediff(end_date,start_date) < 29 then '단기 대여'
    else '장기 대여'
    end as 'rent_type'
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where start_date like "2022-09%"
order by history_id desc

 

조회수가 가장 많은 중고 거래 게시판

- concat , join, 서브 쿼리

SELECT CONCAT("/home/grep/src/", B.BOARD_ID, "/", F.FILE_ID, F.FILE_NAME, F.FILE_EXT) AS FILE_PATH
FROM USED_GOODS_BOARD AS B
	JOIN USED_GOODS_FILE AS F 
		ON B.BOARD_ID = F.BOARD_ID
WHERE VIEWS = (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD)
ORDER BY F.FILE_ID DESC;

 

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

-조인의 여러 방식

# SELECT I.ANIMAL_ID,I.NAME FROM ANIMAL_INS I,ANIMAL_OUTS O
# WHERE I.ANIMAL_ID = O.ANIMAL_ID
# ORDER BY O.DATETIME - I.DATETIME DESC
# LIMIT 2
select i.animal_id, i.name from animal_ins as i
    join animal_outs as o
    on o.animal_id = i.animal_id
order by o.datetime - i.datetime desc
limit 2

 

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

- group by 위치로 지정시 자동으로 pk로 지정, substring + concat

-- 코드를 입력하세요
SELECT u.user_id, u.nickname,
concat(u.city," ",u.street_address1," ",u.street_address2) as 전체주소,
concat(substring(u.tlno,1,3),"-",substring(u.tlno,4,4),"-",substring(u.tlno,8)) as 전화번호
from used_goods_user u
    join used_goods_board b
    on u.user_id = b.writer_id
group by 1
having count(b.writer_id) >= 3
order by 1 desc