|
원본 데이터는 아래와 같습니다.
============= 원 본 ==============
WITH t AS
(
SELECT '총무/넷메신저' category FROM dual
UNION ALL SELECT '총무/전화사용법' FROM dual
UNION ALL SELECT '총무' FROM dual
UNION ALL SELECT '인사' FROM dual
UNION ALL SELECT '인사/구인' FROM dual
UNION ALL SELECT '인사/성희롱 예방교육' FROM dual
UNION ALL SELECT '인사/4대보험' FROM dual
)
select * from t;
결과는 아래와 같이 나와야 합니다.
=============== 가공 데이터 ============
WITH t AS
(
SELECT null parent_seq, 1 seq, null parent_category, '인사' category FROM dual
UNION ALL SELECT 1, 2, '인사', '인사/4대보험' FROM dual
UNION ALL SELECT 1, 3, '인사', '인사/구인' FROM dual
UNION ALL SELECT 1, 4, '인사', '인사/성희롱 예방교육' FROM dual
UNION ALL SELECT null, 5, null, '총무' FROM dual
UNION ALL SELECT 5, 6, '총무', '총무/넷메신저' category FROM dual
UNION ALL SELECT 5, 7, '총무', '총무/전화사용법' category FROM dual
)
select * from t;
제가 만든 쿼리는 다음과 같습니다.
쿼리가 많이 복잡해 보입니다.. 간결한 방법이 없을까요?
알려주세요...
WITH t AS
(
SELECT '총무/넷메신저' category FROM dual
UNION ALL SELECT '총무/전화사용법' FROM dual
UNION ALL SELECT '총무' FROM dual
UNION ALL SELECT '인사' FROM dual
UNION ALL SELECT '인사/구인' FROM dual
UNION ALL SELECT '인사/성희롱 예방교육' FROM dual
UNION ALL SELECT '인사/4대보험' FROM dual
)
select t1.*,t2.rn rn2 from
(select a.*, row_number() over(order by parent_category,category) rn from (
SELECT LENGTH (category) - LENGTH (REPLACE (category, '/', '')) DEPTH,
nvl(SUBSTR (category,1, INSTR (category, '/', -1) - 1),category) parent_category,
category
FROM t) a) t1, (select b.*, row_number() over(order by parent_category,category) rn from (
SELECT LENGTH (category) - LENGTH (REPLACE (category, '/', '')) DEPTH,
nvl(SUBSTR (category,1, INSTR (category, '/', -1) - 1),category) parent_category,
category
FROM t) b) t2 where t1.parent_category=t2.category(+) and t2.rn is not null order by t1.parent_category,t1.category;
|
