[Oracle] 조건에 맞는 사용자 정보 조회하기

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

정답

SELECT
      USER_ID
    , NICKNAME
    , (CITY || ' ' || STREET_ADDRESS1 || ' ' || STREET_ADDRESS2) AS 전체주소
    , CASE WHEN LENGTH(TLNO) = 11 THEN SUBSTR(TLNO,0,3) || '-' || SUBSTR(TLNO,4,4) || '-' || SUBSTR(TLNO,8,4)
    END AS 전화번호
FROM USED_GOODS_USER
WHERE USER_ID IN (
                    SELECT
                        B.USER_ID
                    FROM USED_GOODS_BOARD A, USED_GOODS_USER B
                    WHERE A.WRITER_ID = B.USER_ID
                    GROUP BY B.USER_ID
                    HAVING COUNT(*) >=3
                )
ORDER BY USER_ID DESC;

풀이

Step1) 서브쿼리

SELECT B.USER_ID
FROM USED_GOODS_BOARD A, USED_GOODS_USER B
WHERE A.WRITER_ID = B.USER_ID
GROUP BY B.USER_ID
HAVING COUNT(*) >=3

Step 2) In 연산자

https://lavender1122.tistory.com/324

[Oracle] SQL 서브쿼리 SELECT,FROM,WHERE

Step 3) select 절 문자열 결합

https://lavender1122.tistory.com/323

[Oracle] 문자열 합치는 방법(||, CONCAT())