[SQL][GROUP BY][JOIN][CONCAT][SUBSTR] 조건에 맞는 사용자 정보 조회하기

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

프로그래머스

 

• 내 풀이
  • 01012345678 => 010-1234-5678 로 바꾸려면 SUBSTR하고 CONCAT 으로 묶기
  • CONCAT(SUBSTR(TLNO,1,3),'-',SUBSTR(TLNO,4,4),'-',SUBSTR(TLNO,8)) AS '전화번호'
    • SUBSTR(자를 문자열, 시작위치, 자를 길이)

SELECT USER_ID, NICKNAME, CONCAT(CITY,' ', STREET_ADDRESS1,' ', STREET_ADDRESS2) AS '전체주소', 
		CONCAT(SUBSTR(TLNO,1,3),'-',SUBSTR(TLNO,4,4),'-',SUBSTR(TLNO,8)) AS '전화번호'
FROM USED_GOODS_USER
WHERE USER_ID IN (SELECT WRITER_ID
                 FROM USED_GOODS_BOARD
                 GROUP BY WRITER_ID
                 HAVING COUNT(*)>=3)
GROUP BY USER_ID
ORDER BY 1 DESC

 

 

• 다른 풀이

SELECT USER_ID, NICKNAME, CONCAT(CITY,' ', STREET_ADDRESS1,' ', STREET_ADDRESS2) AS '전체주소',
		CONCAT(SUBSTR(TLNO,1,3),'-',SUBSTR(TLNO,4,4),'-',SUBSTR(TLNO,8)) AS '전화번호'
FROM USED_GOODS_USER AS U
JOIN USED_GOODS_BOARD AS B
ON USER_ID = WRITER_ID
GROUP BY USER_ID
HAVING COUNT(*)>=3
ORDER BY 1 DESC