Oracle_SQL - 계층형 쿼리

 

선생님 자료

https://velog.io/@forbob/Simple-Coding-%EB%8B%B5%EB%B3%80%ED%98%95-%EA%B2%8C%EC%8B%9C%ED%8C%90

 

 

 

 

-- 계층형 쿼리 : level 의사(가상)컬럼 (예: 부모 0 ~ 1,2,3)
SELECT BID AS bid
-- LPAD , || : 문자열 붙이기 
-- 자식에게 * 붙이기
, LPAD('*',(LEVEL-1)) || BOARD_TITLE AS boardTitle
, BOARD_CONTENT AS boardContent
, BOARD_WRITER AS boardWriter
, VIEW_CNT AS viewCnt
, BOARD_GROUP AS boardGroup 
, BOARD_PARENT AS boardParent
FROM TB_REPLY_BOARD
WHERE BOARD_TITLE LIKE '%%'
AND DELETE_YN = 'N'
-- START WITH , CONNECT BY PRIOR
START WITH BOARD_PARENT = 0
-- START WITH BOARD_PARENT(부모컬럼) = 0(최초시작값) : 답변이 안 걸려있으면 0
CONNECT BY PRIOR BID = BOARD_PARENT
-- CONNECT BY PRIOR BID(게시판번호) = BOARD_PARENT(부모 번호) : 자식과 부모와의 관계
-- 계층형 전용 정렬 ( SIBLINGS )
ORDER SIBLINGS BY BOARD_GROUP DESC;

 

결과