select B.USER_ID
,B.NICKNAME
,CONCAT(B.CITY,B.STREET_ADDRESS1,B.STREET_ADDRESS2) '전체주소'
,CONCAT(LEFT(B.TLNO,3),'-',MID(B.TLNO,4,4),'-',RIGHT(B.TLNO,4)) '전화번호'
from (
select *
from USED_GOODS_BOARD
group by writer_id
having count(distinct views) >= 3
) A join USED_GOODS_USER B on A.WRITER_ID = B.USER_ID
order by 1 desc;
SQL
복사
select writer_id
,B.nickname
,sum(price) as total_sales
from USED_GOODS_BOARD A
inner join USED_GOODS_USER B on A.writer_id = B.user_id
group by writer_id, nickname, status
having status = 'DONE'
and sum(price) >= 700000
order by total_sales;
SQL
복사
SELECT distinct(A.car_id) as CAR_ID
from CAR_RENTAL_COMPANY_CAR A
inner join CAR_RENTAL_COMPANY_RENTAL_HISTORY B on A.car_id = B.car_id
where A.car_type = '세단'
and month(start_date) = '10'
order by 1 desc;
SQL
복사
select history_id
,round((case when term < 7 then daily_fee * term
when term < 30 then term * (daily_fee - (daily_fee * (select discount_rate
from car_rental_company_discount_plan
where car_type = '트럭' and duration_type = '7일 이상') / 100))
when term < 90 then term * (daily_fee - (daily_fee * (select discount_rate
from car_rental_company_discount_plan
where car_type = '트럭' and duration_type = '30일 이상') / 100))
else term * (daily_fee - (daily_fee * (select discount_rate
from car_rental_company_discount_plan
where car_type = '트럭' and duration_type = '90일 이상') / 100))
end),0) as FEE
from(
select history_id
,A.car_id
,datediff(end_date,start_date)+1 as term
,daily_fee
from CAR_RENTAL_COMPANY_RENTAL_HISTORY A
inner join CAR_RENTAL_COMPANY_CAR B on A.car_id = B.car_id
where B.car_type = '트럭'
) sub
order by FEE desc, history_id desc;
SQL
복사
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)+1 < 30 then '단기 대여'
else '장기 대여'
end as rent_type
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where month(start_date) = '9'
order by history_id desc;
SQL
복사
select car_id
,if(car_id in (select car_id -- 대여중인 car_id
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where '2022-10-16' between start_date and end_date),'대여중','대여 가능') as AVAILABILITY
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
group by car_id
order by car_id desc;
SQL
복사
TIP 적어도 대여중에 속하는 기간이 하나라도 있으면 ‘대여중’ 으로 표시해야되기 때문에 select 절에서 car_id의 조건에 in을 활용했다.
select MONTH
,car_id
,count(*) as RECORDS
from (
SELECT *
,month(start_date) as MONTH
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where month(start_date) >= 8 and month(end_date) <= 10
) sub
group by car_id
having count(*) >= 5
order by 1, 2 desc;
SQL
복사
select month(start_date) as MONTH
,car_id
,count(*) as RECORDS
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where month(start_date) >= 8 and month(end_date) <= 10
and car_id in (
SELECT car_id
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where month(start_date) >= 8 and month(end_date) <= 10
group by car_id
having count(*) >= 5)
group by month(start_date), car_id
having RECORDS > 0
order by MONTH, car_id desc;
SQL
복사
SELECT a.title
,a.board_id
,b.reply_id
,b.writer_id
,b.contents
,date_format(b.created_date, '%Y-%m-%d')
from USED_GOODS_BOARD a
inner join USED_GOODS_REPLY b on a.board_id = b.board_id
where date_format(a.created_date, '%Y-%m') = '2022-10'
order by 6, 1;
SQL
복사
SELECT board_id
,writer_id
,title
,price
,case when status = 'SALE' then '판매중'
when status = 'RESERVED' then '예약중'
else '거래완료' end as STATUS
from USED_GOODS_BOARD
where date_format(created_date, '%Y-%m-%d') = '2022-10-05'
order by 1 desc;
SQL
복사
SELECT *
from CAR_RENTAL_COMPANY_CAR
where options like '%네비게이션%'
order by car_id desc;
SQL
복사
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 2 desc, 1 desc
SQL
복사
SELECT car_type
,count(distinct car_id) as CARS
from CAR_RENTAL_COMPANY_CAR
where options like '%통풍시트%'
or options like '%열선시트%'
or options like '%가죽시트%'
group by car_type
order by 1;
SQL
복사