SQL 66-68.

66. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회

USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성해주세요. 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬해주세요. 기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요. 조회수가 가장 높은 게시물은 하나만 존재

select concat("/home/grep/src/", b.board_id, "/", f.file_id, f.file_name, f.file_ext) as file_path
from used_goods_board b join used_goods_file f on b.board_id = f.board_id
where b.views = (
select max(views)
from used_goods_board)
order by file_id desc

1) 문제에서 조회수가 가장 높은 것을 뽑으라고 햇으므로 where절에 views만 넣으면 됨 + 조회수가 가장 높은 게시물은 하나만 존재한다는 뜻은 in을 쓸 필요 없이 = 을 조회하면 된다는 것

2) concat( " " , " ") 순서대로 정렬해야 하며, b.board_id와 f.file_id 중간에 "/" 추가해야 함

3) file_id 기준 내림차순 정렬

 

67.주문량이 많은 아이스크림들 조회하기 

7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성

select flavor
from (
select f.flavor,
       f.total_order + ifnull(j.july_sum, 0) as total_sum
from first_half f left join (
select flavor, sum(total_order) as july_sum
from july
group by flavor) j on f.flavor = j.flavor
order by total_sum desc
limit 3 ) a

1) 7월 주문량 집계 (서브쿼리 j)

JULY 테이블에서 각 맛별로 7월 주문량을 모두 합산해야 함 (예를 들어 딸기가 7월에 두 번 출하되어 각각 표기된 것을 합쳐야 함)

select flavor, sum(total_order) as july_sum from july group by flavor

 

2) 상반기와 7월 주문량 합산(left join)

상반기엔 주문이 있었지만 7월에는 주문이 없던 맛도 결과에 포함하기 위해 left join 필요

total_order 를 합친(+) 값들 조회! sum()은 괄호에 컬럼 하나밖에 못 들어가므로, +를 써야 함

참고로 7월 주문량이 없었던 경우(null), 0으로 대체

f.total_order + ifnull(j.july_sum, 0) as total_sum

 

3) 정렬 및 상위 3개 추출

order by total_sum desc limit 3

 

4) 외부 select로 flavor만 추출(서브쿼리 a)

 

728x90

68. 저자 별 카테고리 별 매출액 집계하기

2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성.
결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬

select a.author_id,
       a.author_name,
       b.category,
       sum(b.price * s.sales) as total_sales
from author a 
join book b on a.author_id = b.author_id
join book_sales s on b.book_id = s.book_id
where year(s.sales_date) = 2022 and month(s.sales_date) = 01
group by 1, 2, 3
order by 1 asc, 3 desc

중요

price * sales만 하면 각 판매 건별로 매출액을 계산 = group by로 묶었을 때 여러 판매 건이 있으면 매출액이 합산되지 않음!

그래서 sum()을 해주는 것 필수

+) 날짜의 경우, sales_date between '2022-01-01' and '2022-01-31'이 더 좋다고 함