[Programmers] SQL > Join 풀이 모음

SELECT 
    BOOK.BOOK_ID,
    AUTHOR.AUTHOR_NAME,
    DATE_FORMAT(BOOK.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM 
    BOOK
INNER JOIN 
    AUTHOR
ON 
    BOOK.AUTHOR_ID = AUTHOR.AUTHOR_ID
WHERE
    BOOK.CATEGORY = '경제'
ORDER BY 
    BOOK.PUBLISHED_DATE ASC;

SELECT 
    MEMBER_PROFILE.MEMBER_NAME,
    REST_REVIEW.REVIEW_TEXT,
    DATE_FORMAT(REST_REVIEW.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM
    MEMBER_PROFILE
INNER JOIN
    REST_REVIEW
ON
    MEMBER_PROFILE.MEMBER_ID = REST_REVIEW.MEMBER_ID
WHERE
    MEMBER_PROFILE.MEMBER_ID IN
    (
        SELECT MEMBER_ID
        FROM REST_REVIEW
        GROUP BY MEMBER_ID
        HAVING count(member_id) = (SELECT COUNT(REVIEW_TEXT) AS 'NUM'
    	        FROM REST_REVIEW
                GROUP BY MEMBER_ID
                ORDER BY NUM DESC
                LIMIT 1))
ORDER BY 
    REST_REVIEW.REVIEW_DATE ASC;

SELECT P.PRODUCT_CODE, SUM(P.PRICE * O.SALES_AMOUNT) AS SALES
FROM PRODUCT P
JOIN (SELECT PRODUCT_ID, SALES_AMOUNT FROM OFFLINE_SALE) AS O
ON P.PRODUCT_ID = O.PRODUCT_ID
GROUP BY P.PRODUCT_ID
ORDER BY SALES DESC, P.PRODUCT_CODE