[SQL][프로그래머스] 조건에 맞는 사용자 정보 조회하기 - INNER JOIN + GROUP BY HAVING + COUNT + CONCAT + SUBSTR MYSQL

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

프로그래머스

코드설명

INNER JOIN + GROUP BY HAVING + COUNT + CONCAT + SUBSTR  를 활용합니다.

 

SUBSTR(문자열, INT POS, INT HOWMUCH) : pos 위치에서 howmuch 까지의 문자열을 반환합니다.

코드

-- 코드를 입력하세요
SELECT UGU.USER_ID, UGU.NICKNAME, 
CONCAT(UGU.CITY, ' ', UGU.STREET_ADDRESS1, ' ', UGU.STREET_ADDRESS2) AS '전체주소', 
CONCAT(SUBSTR(UGU.TLNO, 1, 3), '-', SUBSTR(UGU.TLNO, 4, 4), '-', SUBSTR(UGU.TLNO,8, 4)) AS '전화번호'
FROM USED_GOODS_BOARD AS UGB
INNER JOIN USED_GOODS_USER AS UGU
ON UGB.WRITER_ID = UGU.USER_ID
GROUP BY UGU.USER_ID
HAVING COUNT(UGB.BOARD_ID) >= 3
ORDER BY UGU.USER_ID DESC

 

ORACLE

ORACLE에서는 GROUP BY 시에는 항상 SELECT 절에 들어갈 데이터들이 포함되어있어야 합니다. 

SELECT 
    UGU.USER_ID
    , UGU.NICKNAME
    , ( UGU.CITY || ' ' || UGU.STREET_ADDRESS1 || ' ' || UGU.STREET_ADDRESS2 ) AS 전체주소
    , SUBSTR(UGU.TLNO,1,3) || '-' || SUBSTR(UGU.TLNO,4, 4) || '-' || SUBSTR(UGU.TLNO, 8, 4) AS 전화번호
FROM USED_GOODS_USER UGU
INNER JOIN USED_GOODS_BOARD UGB
ON UGU.USER_ID = UGB.WRITER_ID
GROUP BY UGU.USER_ID, UGU.NICKNAME, UGU.CITY, UGU.STREET_ADDRESS1, UGU.STREET_ADDRESS2, UGU.TLNO
HAVING COUNT(UGB.BOARD_ID) >= 3
ORDER BY UGU.USER_ID DESC