[26.02.24 코테(MySQL/Oracle)]-조회수가 가장 많은 중고 게시판의 첨부파일 조회하기

Lv3. 조회수가 가장 많은 중고 게시판의 첨부파일 조회하기 (MySQL)

링크: https://school.programmers.co.kr/learn/courses/30/lessons/164671

 

내가 작성한 코드

-- 풀이1 (서브쿼리)
SELECT CONCAT('/home/grep/src/', BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE 
WHERE BOARD_ID = (SELECT BOARD_ID
                  FROM USED_GOODS_BOARD
                  ORDER BY VIEWS DESC
                  LIMIT 1)
ORDER BY FILE_ID DESC;

-- 풀이2 (WITH절)
WITH BEST_POST AS(SELECT BOARD_ID
                  FROM USED_GOODS_BOARD
                  ORDER BY VIEWS DESC
                  LIMIT 1)
SELECT CONCAT('/home/grep/src/', F.BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE F
JOIN BEST_POST B
  ON F.BOARD_ID = B.BOARD_ID
ORDER BY F.FILE_ID DESC;

 

Oracle

-- 풀이 (서브쿼리)
SELECT '/home/grep/src/' || BOARD_ID || '/' || FILE_ID || FILE_NAME || FILE_EXT AS FILE_PATH
FROM USED_GOODS_FILE
WHERE BOARD_ID = (SELECT BOARD_ID
                  FROM USED_GOODS_BOARD
                  ORDER BY VIEWS DESC
                  FETCH FIRST 1 ROWS ONLY)
ORDER BY FILE_ID DESC

-- FETCH FIRST 1 ROWS ONLY : 딱 첫번째 행 하나만 가져온다
-- MySQL의 LIMIT 1과 같은 역할