[프로그래머스 - 조건에 맞는 사용자 정보 조회하기] - Level 3 Oracle 풀이

 

https://school.programmers.co.kr/learn/courses/30/lessons/164670?language=oracle

프로그래머스

-- 코드를 입력하세요
WITH ID_LIST AS (
SELECT WRITER_ID from USED_GOODS_BOARD A, USED_GOODS_USER  B
WHERE A.WRITER_ID = B.USER_ID
GROUP BY A.WRITER_ID
HAVING count(*) >=3
    )
    
SELECT U.USER_ID, U.NICKNAME ,CONCAT(U.CITY,CONCAT(' ',CONCAT(CONCAT(U.STREET_ADDRESS1,' '),U.STREET_ADDRESS2))) AS 전체주소 , 
SUBSTR(U.TLNO,1,3)||'-'||SUBSTR(U.TLNO,4,4)||'-'||SUBSTR(U.TLNO,8,4) AS 전화번호
FROM USED_GOODS_USER  U
WHERE U.USER_ID IN (SELECT WRITER_ID FROM ID_LIST)
ORDER BY U.USER_ID DESC

/* WITH 테이블 컬럼 참조 IN에서  'SELECT WRITER_ID FROM ID_LIST'*/
/* SUBSSTR(col,n1,n2) 1,3 1번부터 3자리가져옴 */
/* CONCAT은 2개의 인자값만 가능 */