[SQL] 조건에 맞는 사용자와 총 거래금액 조회하기

문제 설명

다음은 중고 거래 게시판 정보를 담은 USED_GOODS_BOARD 테이블과 중고 거래 게시판 사용자 정보를 담은 USED_GOODS_USER 테이블입니다. USED_GOODS_BOARD 테이블은 다음과 같으며 BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS는 게시글 ID, 작성자 ID, 게시글 제목, 게시글 내용, 가격, 작성일, 거래상태, 조회수를 의미합니다.

작성쿼리 -> GROUP BY, HAVING 조건 헷갈림

SELECT USER_ID, NICKNAME, SUM(PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD A, USED_GOODS_USER B
WHERE A.WRITER_ID = B.USER_ID 
AND A.STATUS = 'DONE'
GROUP BY USER_ID 
HAVING SUM(PRICE) >= 700000
ORDER BY TOTAL_SALES ASC

서칭쿼리(Oracle) _ (서브쿼리) WHERE 조건 [실무]

SELECT USER_ID, NICKNAME, TOTAL_PRICE
FROM USED_GOODS_USER, (SELECT WRITER_ID, SUM(PRICE) AS TOTAL_PRICE
    FROM USED_GOODS_BOARD
    GROUP BY WRITER_ID,STATUS
    HAVING STATUS = 'DONE'
    AND SUM(PRICE) >= 700000) B
WHERE B.WRITER_ID = USER_ID
ORDER BY TOTAL_PRICE;

서칭쿼리(Oracle) _ JOIN

SELECT U.USER_ID, U.NICKNAME, SUM(B.PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD B 
    JOIN USED_GOODS_USER U
ON B.WRITER_ID = U.USER_ID
WHERE B.STATUS = 'DONE'
GROUP BY U.USER_ID, NICKNAME
HAVING SUM(B.PRICE) >= 700000
ORDER BY TOTAL_SALES ASC;

SELECT USER_ID, NICKNAME, TOTAL_PRICE
FROM USED_GOODS_USER
JOIN (SELECT WRITER_ID, SUM(PRICE) AS TOTAL_PRICE
    FROM USED_GOODS_BOARD
    GROUP BY WRITER_ID,STATUS
    HAVING STATUS = 'DONE'
    AND SUM(PRICE) >= 700000) B
ON B.WRITER_ID = USER_ID
ORDER BY TOTAL_PRICE;

• Mysql

서칭쿼리(MYSQL) _ WITH 구문사용 [실무]

WITH 
TEMP_01 AS
(
SELECT B.WRITER_ID, B.STATUS, SUM(B.PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD AS B
WHERE STATUS = 'DONE'
GROUP BY WRITER_ID
)

SELECT U.USER_ID, U.NICKNAME, T.TOTAL_SALES
FROM USED_GOODS_USER AS U
LEFT JOIN TEMP_01 AS T
ON U.USER_ID = T.WRITER_ID
WHERE T.TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES ASC

서칭쿼리(MYSQL) _ WHERE 사용

SELECT USER_ID, NICKNAME, SUM(PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD, USED_GOODS_USER
WHERE WRITER_ID = USER_ID AND STATUS = "DONE"
GROUP BY USER_ID 
HAVING SUM(PRICE) >= 700000
ORDER BY TOTAL_SALES ASC