[SQL문제풀기] 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

문제

MYSQL

내가 작성한 정답1 - limit

select concat('/home/grep/src/',b.board_id,'/',b.file_id,b.file_name,b.file_ext) FILE_PATH
from (select board_id 
      from used_goods_board
      order by views desc
      limit 1) a  
join USED_GOODS_FILE b on a.board_id = b.board_id
order by file_id desc

내가 작성한 정답2 - max

with board as (select board_id 
               from USED_GOODS_BOARD
               where views = (select max(views) from USED_GOODS_BOARD))

select concat('/home/grep/src/',b.board_id,'/',b.file_id,b.file_name,b.file_ext) FILE_PATH
from board a  
join USED_GOODS_FILE b on a.board_id = b.board_id
order by file_id desc

내가 작성한 정답3 - rank

with board as (select board_id     
                from (select board_id,RANK() OVER (ORDER BY views DESC) AS view_rank
                        from USED_GOODS_BOARD) tb
                where view_rank = 1)

select concat('/home/grep/src/',board_id,'/',file_id,file_name,file_ext) FILE_PATH
from USED_GOODS_FILE 
where board_id = (select * from board)
order by file_id desc

ORACLE

내가 작성한 정답1 - rownum

with board as (select *
                from (SELECT board_id
                        from USED_GOODS_BOARD
                        order by views desc)
                where rownum = 1)

select '/home/grep/src/'||board_id||'/'||file_id||file_name||file_ext  FILE_PATH
from USED_GOODS_FILE 
where board_id = (select * from board)
order by file_id desc

내가 작성한 정답 2 - max

with board as (select board_id
                from USED_GOODS_BOARD
               where views = (SELECT max(views) from USED_GOODS_BOARD))

select '/home/grep/src/'||b.board_id||'/'||b.file_id||b.file_name||b.file_ext  FILE_PATH
from board a 
join USED_GOODS_FILE b on a.board_id = b.board_id
order by file_id desc

내가 작성한 정답3 - rank

with board as (select board_id     
                from (select board_id,RANK() OVER (ORDER BY views DESC) AS view_rank
                        from USED_GOODS_BOARD)
                where view_rank = 1)

select '/home/grep/src/'||board_id||'/'||file_id||file_name||file_ext  FILE_PATH
from USED_GOODS_FILE 
where board_id = (select * from board)
order by file_id desc