[26.02.19 코테(MySQL)]-조건에 맞는 사용자 정보 조회하기

Lv3. 조건에 맞는 사용자 정보 조회하기 (MySQL)

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

 

내가 작성한 코드

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

-- 풀이2 (정규표현식(REGEXP_REPLACE))
-- (.{3}) : 3글자 그룹, (.{4}) : 4글자 그룹, (.{4}) : 마지막 4글자 그룹
SELECT U.USER_ID, U.NICKNAME 
       ,CONCAT_WS(' ', CITY, STREET_ADDRESS1, STREET_ADDRESS2) AS '전체주소'
       ,REGEXP_REPLACE(U.TLNO, '(.{3})(.{4})(.{4})', '$1-$2-$3') AS '전화번호'
FROM USED_GOODS_USER U
JOIN (SELECT WRITER_ID
      FROM USED_GOODS_BOARD
      GROUP BY WRITER_ID
      HAVING COUNT(*) >= 3) B
  ON B.WRITER_ID = U.USER_ID
ORDER BY U.USER_ID DESC;

 

Oracle

-- Oracle
SELECT U.USER_ID, U.NICKNAME
       ,U.CITY || ' ' || U.STREET_ADDRESS1 || ' ' || U.STREET_ADDRESS2 AS "전체주소"
       ,REGEXP_REPLACE(U.TLNO, '(.{3})(.{4})(.{4})', '\1-\2-\3') AS "전화번호"
FROM USED_GOODS_USER U
JOIN (SELECT WRITER_ID
      FROM USED_GOODS_BOARD
      GROUP BY WRITER_ID
      HAVING COUNT(*) >= 3) B
  ON U.USER_ID = B.WRITER_ID
ORDER BY U.USER_ID DESC

 

Oracle vs MySQL

• 문자열 연결
  • CONCAT (MySQL)
  • || (Oracle)
• 정규표현식 참조
  • \ (Oracle)
  • $ (MySQL)