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

문제 설명

다음은 중고 거래 게시판 정보를 담은 USED_GOODS_BOARD 테이블과 중고 거래 게시판 첨부파일 정보를 담은 USED_GOODS_USER 테이블입니다.

USED_GOODS_BOARD 테이블

Column name	Type	Nullable	Description
BOARD_ID	VARCHAR(5)	FALSE	게시글 ID
WRITER_ID	VARCHAR(50)	FALSE	작성자 ID
TITLE	VARCHAR(100)	FALSE	게시글 제목
CONTENTS	VARCHAR(1000)	FALSE	게시글 내용
PRICE	NUMBER	FALSE	가격
CREATED_DATE	DATE	FALSE	작성일
STATUS	VARCHAR(10)	FALSE	거래상태
VIEWS	NUMBER	FALSE	조회수

USED_GOODS_USER 테이블

Column name	Type	Nullable	Description
USER_ID	VARCHAR(50)	FALSE	회원 ID
NICKNAME	VARCHAR(100)	FALSE	닉네임
CITY	VARCHAR(100)	FALSE	시
STREET_ADDRESS1	VARCHAR(100)	FALSE	도로명 주소
STREET_ADDRESS2	VARCHAR(100)	TRUE	상세 주소
TLNO	VARCHAR(20)	FALSE	전화번호

---

문제

USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 중고 거래 게시물을 3건 이상 등록한 사용자의 사용자 ID, 닉네임, 전체주소, 전화번호를 조회하는 SQL문을 작성해주세요. 이때, 전체 주소는 시, 도로명 주소, 상세 주소가 함께 출력되도록 해주시고, 전화번호의 경우 xxx-xxxx-xxxx 같은 형태로 하이픈 문자열(-)을 삽입하여 출력해주세요. 결과는 회원 ID를 기준으로 내림차순 정렬해주세요.

---

풀이 과정

1. 테이블 결합
   • WRITER_ID와 USER_ID를 기준으로 INNER JOIN
2. 그룹화 및 게시물 수 계산
   • GROUP BY를 사용하여 사용자별로 그룹화하고, COUNT(BOARD_ID)를 통해 각 사용자의 게시물수를 계산해야지
   • HAVING 절을 사용하여 게시물 수가 3건 이상인 사용자만 걸러내자
3. 전체 주소 생성 및 전화번호 형식 변경
   • CONCAT 함수를 사용하여 CITY, STREET_ADDRESS1, STREET_ADDRESS2 결합하여 전체 주소 생성
   • IFNULL을 사용하여 NULL 값이 있는 상세 주소를 빈 문자열로 대체 (중요..)
   • SUBSTR과 CONCAT을 사용하여 전화번호에 하이픈(-) 삽입
4. 정렬
   • 정렬 기준에 따라 ORDER BY로 결과 정렬
     • USER_ID 기준으로 내림차순 정렬

• 교훈
  • 문자열 처리 CONCAT, SUBSTR 자주 쓰이므로 기억합시다.
  • NULL 값이 있는 상세 주소를 빈 문자열로 대체하는 것. (중요,,) 이거 안 하면 다 NULL 되므로 오류.
    Oracle에서는 NVL로 사용!!!

---

정답

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

---

Tip

그룹화와 문자열 처리

• GROUP BY를 사용하여 사용자별로 데이터를 그룹화하고 HAVING 절로 조건을 적용
• CONCAT, SUBSTRING : 문자열을 결합하거나 원하는 형식으로 변환하기
• IFNULL : NULL 값을 대체할 때 사용

---

개념 확인

• GROUP BY 절과 HAVING 절
  • GROUP BY를 통해 동일한 값들로 그룹화하고 HAVING 절을 사용해 집계 조건을 적용
• CONCAT 함수
  • 여러 문자열을 하나로 결합합니다. 예: CONCAT(‘A’, ‘B’, ‘C’) → ‘ABC’
• SUBSTR 함수
  • 문자열에서 특정 위치의 하위 문자열을 추출
  • ex) **SUBSTR('01053422914', 1, 3)** → '010'
• IFNULL 함수(MySQL)
  • NULL 값을 지정된 대체 값으로 변환
  • 오라클은 NVL 처리

---

Useful link

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