데이터베이스 질문과 답변
SELECT t.nm
, t.cd
, t.parent_cd
, LEVEL
, CONNECT_BY_ISLEAF
FROM (
SELECT 'a' AS nm, 'a' AS cd, NULL AS parent_cd FROM dual union ALL
SELECT 'a-a' AS nm, 'aa' AS cd, 'a' AS parent_cd FROM dual union ALL
SELECT 'a-a-지우개' AS nm, 'aa1' AS cd, 'aa' AS parent_cd FROM dual union ALL
SELECT 'a-a-연필' AS nm, 'aa2' AS cd, 'aa' AS parent_cd FROM dual union ALL
SELECT 'a-a-샤프' AS nm, 'aa3' AS cd, 'aa' AS parent_cd FROM dual union ALL
SELECT 'b' AS nm, 'b' AS cd, NULL AS parent_cd FROM dual union ALL
SELECT 'b-b' AS nm, 'bb' AS cd, 'b' AS parent_cd FROM dual union ALL
SELECT 'b-b-샴푸' AS nm, 'bb1' AS cd, 'bb' AS parent_cd FROM dual union ALL
SELECT 'b-b-비누' AS nm, 'bb2' AS cd, 'bb' AS parent_cd FROM dual union ALL
SELECT 'b-b-바디워시a' AS nm, 'bb3' AS cd, 'bb' AS parent_cd FROM dual
) t
WHERE 1=1
AND t.nm LIKE '%a%'
START WITH t.parent_cd IS NULL
CONNECT BY PRIOR t.cd = t.parent_cd
위 쿼리를 실행하면 'b-b-바디워시a' 데이터는 부모 없이 포함되어 나오게 됩니다.
'b-b-바디워시a' 외 나머지 데이터들은 전부 a가 포함되어 있기 때문에 계층형으로 전부 나옵니다.
그런데 뷰 단에서 트리 형태로 보여줄때 부모가 없으니 마치 a의 자식인 것처럼 나오고 있습니다.
뷰 컴포넌트를 수정하기는 어려운 상황이라 쿼리 결과에서 'b-b-바디워시a'의 부모까지 같이 포함하여 나오게 할 수 있는 방법이 있을까요?
by 마농
[2023.05.16 20:12:15]
WITH t AS
(
SELECT 'a' nm, 'a' cd, NULL parent_cd FROM dual
UNION ALL SELECT 'a-a' , 'aa' , 'a' FROM dual
UNION ALL SELECT 'a-a-지우개' , 'aa1', 'aa' FROM dual
UNION ALL SELECT 'a-a-연필' , 'aa2', 'aa' FROM dual
UNION ALL SELECT 'a-a-샤프' , 'aa3', 'aa' FROM dual
UNION ALL SELECT 'b' , 'b' , NULL FROM dual
UNION ALL SELECT 'b-b' , 'bb' , 'b' FROM dual
UNION ALL SELECT 'b-b-샴푸' , 'bb1', 'bb' FROM dual
UNION ALL SELECT 'b-b-비누' , 'bb2', 'bb' FROM dual
UNION ALL SELECT 'b-b-바디워시a', 'bb3', 'bb' FROM dual
)
SELECT nm
, cd
, parent_cd
, LEVEL lv
, CONNECT_BY_ISLEAF isleaf
, SYS_CONNECT_BY_PATH(nm, '/') nm_path
FROM (SELECT DISTINCT nm, cd, parent_cd
FROM t
START WITH nm LIKE '%a%'
CONNECT BY PRIOR parent_cd = cd
)
START WITH parent_cd IS NULL
CONNECT BY PRIOR cd = parent_cd
;
by jake3371
[2023.05.17 19:20:53]
감사합니다
) 버튼을 클릭하여 작성 하시면 됩니다.