❓ Question ❗ Answer # join key : book.author_id = author.author_id / book.book_id = book_sales.book_id # 조건 : DATE_FORMAT(book_sales.sales_date,'%Y-%m') = '202201' SELECT author.author_id, author.author_name, b_s.category, SUM(b_s.price*b_s.sales) FROM author INNER JOIN (SELECT book.author_id, book.category, book.price, sales.sales FROM book INNER JOIN book_sales sales ON book.book_id = sales.bo..
❓ Question ❗ Answer SELECT CONCAT('/home/grep/src/',board.board_id,'/',file.file_id,file.file_name,file.file_ext) FILE_PATH FROM used_goods_board board INNER JOIN used_goods_file file ON board.board_id = file.board_id WHERE board.views = (SELECT MAX(views) FROM used_goods_board) ORDER BY file.file_id DESC; 📌 Discussion - INNER JOIN을 활용하여 같은 게시물 ID를 사용하는 모든 파일 레코드 호출 - sub-query를 scalar 형태로 사용하여 ..
❓ Question ❗ Answer SELECT f.flavor FROM first_half f INNER JOIN july j ON f.flavor = j.flavor GROUP BY f.flavor ORDER BY SUM(f.total_order+j.total_order) DESC LIMIT 3;📌 Discussion flavor로 join하여 shipment_id가 다르거나, shipment_id가 같더라도 테이블이 다르면 서로 다른 레코드로 취급함 group by로 같은 flavor끼리 묶어서 위 조건에 해당되는 서로 다른 레코드를 합계
❓ Question ❗ Answer SELECT history_id, car_id, DATE_FORMAT(start_date,'%Y-%m-%d'), DATE_FORMAT(end_date,'%Y-%m-%d'), IF(DATEDIFF(end_date,start_date)+1>=30,'장기 대여', '단기 대여') RENT_TYPE FROM car_rental_company_rental_history WHERE start_date LIKE '2022-09%' ORDER BY history_id DESC;📌 Discussion IF로 조건문으로 rent_type 표시 대여일수 = end_date - start_date + 1
❓ Question ❗ Answer # GROUP_CONCAT 활용 SELECT cart_id FROM cart_products GROUP BY cart_id HAVING GROUP_CONCAT(name) LIKE '%Milk%' AND GROUP_CONCAT(name) LIKE '%Yogurt%' ORDER BY cart_id; # sub query 활용 SELECT DISTINCT cart_id FROM cart_products WHERE name = 'Milk' AND cart_id in (SELECT cart_id FROM cart_products WHERE name = 'Yogurt') ORDER BY cart_id; # IN, DISTN..
❓ Question ❗ Answer SELECT * FROM places WHERE host_id IN (SELECT host_id FROM places GROUP BY host_id HAVING COUNT(*)>=2) ORDER BY id;📌 Discussion sub query 활용 하여 조건에 맞는 host_id에 해당하는 데이터만 출력
❓ 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..
