❓ Question ❗ Answer SELECT car_id, ROUND(AVG(DATEDIFF(end_date,start_date)+1),1) AVERAGE_DURATION FROM car_rental_company_rental_history GROUP BY car_id HAVING AVG(DATEDIFF(end_date,start_date)+1) >= 7.0 ORDER BY AVERAGE_DURATION DESC, car_id DESC;📌 Discussion DATEDIFF(나중일자,먼저일자)로 일수를 계산할 수 있다. 날짜 계산 관련 링크 : https://extbrain.tistory.com/78 빌린 기간 = 반납 일시 - 빌린 일시 + 1 HAVING에 조건을 걸어줄 때는 반올림하기 전의 값이..
❓ Question ❗ Answer # JOIN KEY : p.pt_no = a.pt_no / a.mddr_id = d.dr_id # 문제 조건 # 1) 2022-04-13 : a.apnt_ymd LIKE '2022-04-13%' # 2) 취소되지않은 : a.apnt_cncl_yn = 'N' # 3) 흉부외과(CS) : a.mcdp_cd = 'CS' # 4) 출력 : a.apnt_no 진료예약번호, p.pt_name 환자이름, p.pt_no 환자번호, # a.mcdp_cd 진료과코드, d.dr_name 의사이름, a.apnt_ymd 진료예약일시 SELECT ad.apnt_no 진료예약번호, p.pt_name 환자이름, p.pt_no 환자번호, ad.mcdp_cd..
❓ Question ❗ Answer SELECT DISTINCT(car_id) CAR_ID , IF(car_id IN (SELECT car_id FROM car_rental_company_rental_history WHERE '2022-10-16' BETWEEN start_date and end_date), '대여중','대여 가능') AVAILABILITY FROM car_rental_company_rental_history ORDER BY CAR_ID DESC;📌 Discussion 하나의 car_id에 대해 여러 렌탈 기록이 존재하여 대여 가능 여부가 여러가지가 나온다. 대여 중인 car_id의 레코드를 sub-query로 불러와서 IF 절로 하나의 car_..
❓ Question ❗ Answer SELECT YEAR(sale.sales_date) YEAR , MONTH(sale.sales_date) MONTH , info.gender GENDER , COUNT(DISTINCT(info.user_id)) USERS FROM user_info info INNER JOIN online_sale sale ON info.user_id = sale.user_id WHERE info.gender IS NOT NULL GROUP BY YEAR, MONTH, GENDER ORDER BY YEAR, MONTH, GENDER;📌 Discussion 동일한 회원이 여러 날에 거쳐서 구매한 경우에도 한 명의 회원으로 집계해야하므로 COUNT(DISTINCT)로 중복제거 후 집계
❓ Question ❗ Answer SELECT info.rest_id, info.rest_name, info.food_type, info.favorites, info.address, ROUND(AVG(review.review_score),2) score FROM rest_info info INNER JOIN rest_review review ON info.rest_id = review.rest_id WHERE info.address LIKE '서울%' GROUP BY rest_id ORDER BY score DESC, info.favorites DESC;📌 Discussion WHERE 문에서 join을 시키고 조건을 달아준 뒤 집계를 위해서 group by를 해주고, ROUND로 반올림..
❓ Question ❗ Answer SELECT board_id, writer_id, title, price, (CASE WHEN status = 'SALE' THEN '판매중' WHEN status = 'RESERVED' THEN '예약중' ELSE '거래완료' END) status FROM used_goods_board WHERE created_date = '2022-10-05' ORDER BY board_id DESC;📌 Discussion CASE WHEN THEN ELSE END 구문으로 조건별로 분류
❓ Question ❗ Answer SELECT * FROM car_rental_company_car WHERE options LIKE '%네비게이션%' ORDER BY car_id DESC;📌 Discussion LIKE으로 '네비게이션'이 options에 포함된 레코드 불러오기
❓ Question ❗ Answer SELECT users.user_id USER_ID , users.nickname NICKNAME , CONCAT_WS(' ', users.CITY, users.street_address1, users.street_address2) 전체주소 , CONCAT_WS('-', LEFT(users.tlno,3), SUBSTRING(users.tlno,4,4), RIGHT(users.tlno,4)) 전화번호 FROM used_goods_board boards INNER JOIN used_goods_user users ON boards.writer_id = users.user_id GROUP BY USER_ID HAVING COUNT(USER_ID) ..
