데이터베이스 질문과 답변
with t as ( select '1' AS ID, null AS P_ID, 'aa' AS text from dual union all select '11' AS ID, '1' AS P_ID, 'bb' AS text from dual union all select '111' AS ID, '11' AS P_ID, 'cc' AS text from dual ) select * from t where text = 'BB'
위와 비슷한 구조인데...흐음 이것저것 고민해보고 있는데요..
본문의 text나 댓글의 text 대댓글의 text 어느걸로 검색조건을 타도 본문에서 대댓글 전부 다 조회되게 하고 싶은데..
중복이 나거나 뭔가 누락되거나 계속 헤메이네요..;;
도움 부탁드립니다. ㅠ
by 마농
[2021.02.06 08:10:05]
WITH t AS
(
SELECT 1 id, null p_id, 'aa' text FROM dual
UNION ALL SELECT 11, 1, 'bb' FROM dual
UNION ALL SELECT 111, 11, 'cc' FROM dual
UNION ALL SELECT 12, 1, 'bb' FROM dual
UNION ALL SELECT 121, 12, 'cc' FROM dual
UNION ALL SELECT 13, 1, 'dd' FROM dual
UNION ALL SELECT 131, 12, 'ee' FROM dual
)
SELECT id
, p_id
, text
, LEVEL lv
FROM (SELECT id, p_id, text
FROM t
START WITH UPPER(text) LIKE UPPER('%'||'BB'||'%')
CONNECT BY PRIOR p_id = id
UNION
SELECT id, p_id, text
FROM t
START WITH UPPER(text) LIKE UPPER('%'||'BB'||'%')
CONNECT BY PRIOR id = p_id
)
START WITH p_id IS NULL
CONNECT BY PRIOR id = p_id
;
) 버튼을 클릭하여 작성 하시면 됩니다.