sql_코드카타(2023.12.21) 3개 테이블 join, max(if)

53. ONLINE_SALE 테이블에서 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성해주세요. 결과는 회원 ID를 기준으로 오름차순 정렬해주시고 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬해주세요.

select user_id,
product_id 
from
(
#동일한 회원이 동일한 상품을 재구매 > concat으로 묶어서 컬럼 추가
select user_id,
product_id,
concat(user_id,'-',product_id) merge,
count(1) cnt_merge
from online_sale
group by 3
) a
where a.cnt_merge >= 2 
order by 1, 2 desc 

54. 가장 최근에 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성해주세요.

SELECT max(datetime)
from animal_ins 

55. USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 중고 거래 게시물을 3건 이상 등록한 사용자의 사용자 ID, 닉네임, 전체주소, 전화번호를 조회하는 SQL문을 작성해주세요. 이때, 전체 주소는 시, 도로명 주소, 상세 주소가 함께 출력되도록 해주시고, 전화번호의 경우 xxx-xxxx-xxxx 같은 형태로 하이픈 문자열(-)을 삽입하여 출력해주세요. 결과는 회원 ID를 기준으로 내림차순 정렬해주세요.

select user_id,
nickname,
concat(city, " ", street_address1," ", street_address2) "전체주소",
concat(substring(tlno,1,3),'-',substring(tlno,4,4),'-',substring(tlno,8,4)) "전화번호"
from 
(
SELECT b.user_id,
    b.nickname,
    b.city,
    b.street_address1,
    b.street_address2,
    b.tlno,
    count(1) cnt_id
from used_goods_board a inner join used_goods_user b on a.writer_id = b.user_id 
group by 1 
) a 
where a.cnt_id >= 3 
order by user_id desc 

56. CAR_RENTAL_COMPANY_CAR 테이블에서 '네비게이션' 옵션이 포함된 자동차 리스트를 출력하는 SQL문을 작성해주세요. 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.

SELECT *
from car_rental_company_car 
where options like '%네비게이션%'
order by car_id desc 

57. USED_GOODS_BOARD 테이블에서 2022년 10월 5일에 등록된 중고거래 게시물의 게시글 ID, 작성자 ID, 게시글 제목, 가격, 거래상태를 조회하는 SQL문을 작성해주세요. 거래상태가 SALE 이면 판매중, RESERVED이면 예약중, DONE이면 거래완료 분류하여 출력해주시고, 결과는 게시글 ID를 기준으로 내림차순 정렬해주세요.

SELECT board_id,
writer_id,
title,
price,
case when status = 'sale' then '판매중'
     when status = 'reserved' then '예약중'
     else '거래완료' end STATUS
from used_goods_board
where date_format(created_date, '%Y-%m-%d') = '2022-10-05'
order by 1 desc 

마지막 where절에서 'where substring(created_date, 1,10) = '2022-10-05'의 구문도 가능하다.

58.PATIENT, DOCTOR 그리고 APPOINTMENT 테이블에서 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회하는 SQL문을 작성해주세요. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 항목이 출력되도록 작성해주세요. 결과는 진료예약일시를 기준으로 오름차순 정렬해주세요.

#서브쿼리를 이용해 세개의 테이블을 합치려 했으나 오류가 난다.
select b.apnt_no,
b.pt_name,
b.pt_no,
b.mcdp_cd,
d.dr_name,
b.apnt_ymd
from b left join doctor on b.mddr_id = d.dr_id 
(
 SELECT apnt_no,
pt_name,
p.PT_NO,
mcdp_cd,
apnt_ymd,
mddr_id 
from appointment a left join patient p on a.pt_no = p.pt_no 
where substring(a.apnt_ymd, 1, 10) = '2022-04-13' and a.apnt_cncl_yn = 'N' and a.mcdp_cd = 'CS' ) b

#이런 경우 left조인을 사용해 3개 테이블을 묶는데, 두개를 엮어주는 appointment를 가운데 써준다.
SELECT apnt_no,
pt_name,
p.PT_NO,
a.mcdp_cd,
d.dr_name,
a.apnt_ymd
from patient p
left join appointment a on p.pt_no = a.pt_no
left join doctor d on a.mddr_id = d.dr_id   
where substring(a.apnt_ymd, 1, 10) = '2022-04-13' and a.apnt_cncl_yn = 'N' and a.mcdp_cd = 'CS'
order by 6 

59. CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요. 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요

#start_date와 end_date가 2022-10-16 이하인지, 이상인지를 따져서 각 행마다 대여 가능여부를 구함. 
select car_id,
date_format(start_date, '%Y-%m-%d') s_date,
date_format(end_date, '%Y-%m-%d') e_date,
case when s_date <='2022-10-16' and e_date >= '2022-10-16' then '대여중'
     else  '대여가능' end "AVAILABILITY"
from 
(
SELECT car_id, 
    start_date,
    end_date,
date_format(start_date, '%Y-%m-%d') s_date,
date_format(end_date, '%Y-%m-%d') e_date
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
) a

그리고 나서 중복되는 car_id가 있다면 대여중은 남기고 대여가능만 있다면 대여가능만 남겨야 하는데 막혔다.
이 부분은 대여 가능여부를 숫자화해서 max를 해서 group by를 써서 해결할 수 있다.

#날짜를 통해 2022-10-16이 사이에 있으면 1, 그렇지 않으면 0
#max와 group by로 최대값만 남겨줌
#if문을 통해 컬럼값 적용
select car_id,
if(b.date,"대여중", "대여 가능") "AVAILABILITY"
from 
(
select car_id,
max(case when start_date <='2022-10-16' and end_date >= '2022-10-16' then 1
     else 0 end) date 
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
group by 1
) b
order by 1 desc