❓ Question ❗ Answer SELECT YEAR(sale.sales_date) year , MONTH(sale.sales_date) month , COUNT(DISTINCT sale.user_id) purchased_users , ROUND(COUNT(DISTINCT sale.user_id)/(SELECT COUNT(*) FROM user_info WHERE YEAR(joined) = '2021'),1) purchased_ratio FROM user_info info INNER JOIN online_sale sale ON info.user_id = sale.user_id WHERE YEAR(info.joined) = '2021' GROUP BY year, month ORDER BY year, m..
❓ Question ❗ Answer SELECT d.history_id, ROUND(d.duration*d.daily_fee*(100-IFNULL(p.discount_rate,0))/100) fee FROM (SELECT r.history_id, r.car_id, r.daily_fee, r.duration, r.car_type, (CASE WHEN r.duration < 7 THEN NULL WHEN r.duration BETWEEN 7 AND 29 THEN '7일 이상' WHEN r.duration BETWEEN 30 AND 89 THEN '30일 이상' ELSE '90일 이상' END) duration_type FROM (SELECT h.history_id, c.car_id, c.car_type, c..
❓ Question ❗ Answer # output : c.car_id, c.car_type, fee((DATEDIFF(h.end_date,h.start_date)+1)*c.daily_fee*d.discount_rate(대여일에 따라 d.duration_type에 맞는 d.discount_rate 입력) # 조건 : c.car_type IN ('세단','SUV'), h.end_date < '2022-11-01' OR h.start_date < '2022-11-30', fee BETWEEN(50,200) # alias : car_rental_company_car c, car_rental_company_rental_history h, car_rental_company_discount_plan d # JOIN..
❓ Question ❗ Answer SELECT b.title, b.board_id, r.reply_id, r.writer_id, r.contents, DATE_FORMAT(r.created_date,'%Y-%m-%d') FROM used_goods_board b INNER JOIN used_goods_reply r ON b.board_id = r.board_id WHERE b.created_date LIKE '2022-10%' ORDER BY r.created_date, b.title; 📌 Discussion created_date라는 column이 양쪽에 모두 있으나 다른 값인 것을 유의하여 조건절에 사용해야한다.
❓ Question ❗ Answer # set 함수 이용 SET @HOUR := -1; # 초기 변수 설정, 세미콜론으로 종료 해줘야함 SELECT (@HOUR := @HOUR+1) HOUR, # HOUR 변수에 +1 반복 (SELECT COUNT(*) FROM animal_outs WHERE HOUR(datetime) = @HOUR) COUNT FROM animal_outs WHERE @HOUR < 23 # 마지막으로 +1한 HOUR가 23이면 반복 멈춤 ORDER BY HOUR; 📌 Discussion SET으로 변수를 설정해주고 @로 반복문을 실행해줌 WHERE 문에서 반복문 중지 조건을 넣어줌
❓ 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 ..
