❓ Question ❗ Answer SELECT DATE_FORMAT(sales_date,'%Y-%m-%d'), product_id, IFNULL(user_id,NULL) user_id, sales_amount FROM (SELECT sales_date, product_id, user_id, sales_amount FROM online_sale UNION SELECT sales_date, product_id, NULL, sales_amount FROM offline_sale) a WHERE sales_date LIKE '2022-03%' ORDER BY sales_date, product_id, user_id; 📌 Discussion sub-query에서 두 테이블로부터 동일한 column명으로 데이터를..
❓ Question ❗ Answer SELECT p.member_name, r.review_text, DATE_FORMAT(r.review_date,'%Y-%m-%d') FROM member_profile p INNER JOIN rest_review r ON p.member_id = r.member_id WHERE p.member_id IN (SELECT member_id FROM rest_review GROUP BY member_id HAVING COUNT(member_id) = (SELECT MAX(cnt) FROM (SELECT COUNT(member_id) cnt FROM rest_review GROUP BY member_id) a)) ORDER BY review_date, review_text;..
❓ Question ❗ Answer SELECT MONTH(start_date) month, car_id, COUNT(car_id) RECORDS FROM car_rental_company_rental_history WHERE car_id IN (SELECT car_id FROM car_rental_company_rental_history WHERE start_date BETWEEN '2022-08-01' AND '2022-10-31' GROUP BY car_id HAVING COUNT(car_id) >= 5) AND start_date BETWEEN '2022-08-01' AND '2022-10-31' GROUP BY month, car_id HAVING RECORDS IS NOT NULL ORDER ..
❓ 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..
