💻 하나씩 차곡차곡/프로그래머스 (SQL)
[String, Date/LEVEL3] 조건에 맞는 사용자 정보 조회하기 (MySQL/Oracle)
뚜루리
2023. 6. 23. 12:30
728x90
320x100
MySQL
SELECT USER_ID
, NICKNAME
, CONCAT(CITY, ' ', STREET_ADDRESS1, ' ', STREET_ADDRESS2) AS 전체주소
, CONCAT(SUBSTR(TLNO, 1, 3), '-', SUBSTR(TLNO, 4, 4), '-', SUBSTR(TLNO, 8, 4) ) AS 전화번호
FROM USED_GOODS_USER
WHERE USER_ID IN (SELECT WRITER_ID
FROM (SELECT *
FROM USED_GOODS_BOARD
GROUP BY WRITER_ID
HAVING COUNT(*) >= 3) A)
ORDER BY USER_ID DESC
Oracle
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;
728x90
320x100