SQL 알고리즘 코드카타 21일차(2) 26.01.19(월)

부제:

조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기, 레벨 5

 

1. 문제 링크:https://school.programmers.co.kr/learn/courses/30/lessons/164671
2. 정답 코드: 

=> JOIN 활용 (가장 높은 조회수를 먼저 찾고, 그와 일치하는 게시글을 필터링)

SELECT
    concat('/home/grep/src/',ugf.board_id,'/',ugf.file_id,ugf.file_name,ugf.file_ext) as file_path
from used_goods_board as ugb
inner join used_goods_file as ugf
    on ugb.board_id = ugf.board_id
where views = (SELECT MAX(views) FROM USED_GOODS_BOARD)
order by ugf.file_id desc;

=> 서브쿼리 활용 (가장 높은 조회수의 BOARD_ID를 뽑는 서브쿼리)

SELECT
    concat('/home/grep/src/',ugf.board_id,'/',ugf.file_id,ugf.file_name,ugf.file_ext) as file_path
from used_goods_file as ugf
where board_id = (
    SELECT BOARD_ID 
    FROM USED_GOODS_BOARD 
    ORDER BY views desc 
    LIMIT 1
)
order by ugf.file_id desc

3. 오류 상황:

SELECT
    concat('/home/grep/src/',ugf.board_id,'/',ugf.file_id,ugf.file_name,ugf.file_ext) as file_path
from used_goods_board as ugb
inner join used_goods_file as ugf
    on ugb.board_id = ugf.board_id
having max(ugb.views)
order by ugf.file_id desc;

=> having 오류

4. 시도 방법:

=>  서브쿼리 활용

5. 최종 문제 해결 방법:

=> HAVING은 GROUP BY와 함께 쓰이며, 특정 그룹의 조건을 필터링할 때 사용