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

[ji-0630]

문제 설명

[ji-0630]

나의 풀이

SELECT u.USER_ID, u.NICKNAME, concat(CITY, " ", STREET_ADDRESS1, STREET_ADDRESS2) as "전체주소", TLNO as "전화번호"
from USED_GOODS_BOARD b right join USED_GOODS_USER u
on u.USER_ID = b.WRITER_ID
group by u.USER_ID
having count(b.BOARD_ID) >=3
order by u.USER_ID

• 글자 포맷 함수를 모름

[ji-0630]

다른 사람의 풀이

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

• CONCAT_WS("-", SUBSTRING(TLNO, 1, 3), SUBSTRING(TLNO, 4, 4), SUBSTRING(TLNO, 8, 4)) AS 전화번호
• concat_ws(구분자, 문자열, ...): 구분자를 기준으로 문자열 합쳐줌
• substring(문자열, 시작자리, 개수): 문자열 중 일부만 가져옴

[ji-0630]

다시 풀기

SELECT u.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_BOARD b right join USED_GOODS_USER u
on b.WRITER_ID = u.USER_ID
group by u.USER_ID
having count(*) >= 3
order by u.USER_ID desc