[SQL][프로그래머스]조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기 - INNER JOIN + SUBQUERY + CONCAT MYSQL ORACLE

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

프로그래머스

코드설명

INNER JOIN + SUBQUERY를 활용합니다.

 

코드

# -- 코드를 입력하세요
SELECT CONCAT('/home/grep/src/',UGB.BOARD_ID, '/',UGF.FILE_ID,UGF.FILE_NAME,UGF.FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE UGF
INNER JOIN (select board_id, ugb.views as 'VIEWS'
from used_goods_board as ugb 
order by ugb.views desc 
limit 1) AS UGB
ON UGF.BOARD_ID = UGB.BOARD_ID
ORDER BY UGF.FILE_ID DESC

 

ORACLE

SELECT '/home/grep/src/' || UGF.BOARD_ID || '/' || UGF.FILE_ID || UGF.FILE_NAME || UGF.FILE_EXT AS FILE_PATH
FROM USED_GOODS_FILE UGF
WHERE BOARD_ID =  (SELECT BOARD_ID FROM USED_GOODS_BOARD
WHERE VIEWS = (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD) )
ORDER BY UGF.FILE_ID DESC