데이터베이스 질문과 답변
원본 데이터는 아래와 같습니다.
============= 원 본 ==============
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;
-- 원 데이터에 seq 값이 있어야 할 것 같은데 없어서 임의로 해봅니다.
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 decode(delim_pos, 0, null, min(rownum) over (partition by nvl(parent_category, category))) parent_seq,
rownum seq,
parent_category,
category
from (
select category,
substr(category, 1, instr(category, '/') - 1) parent_category,
instr(category, '/') delim_pos
from t
order by category
) v;
배움을 주셔서 감사합니다.^^
비주류님이 만드신 쿼리로 테스트해보니 분류명이 '/'를 구분자로 3depth 이상이 되면 부모 일련번호를
최상위 부모의 일련번호를 가져오네요.
그래도 위와 같은 방식으로 풀어 볼 생각을 못했는데 도움주셔서 감사합니다.
-- 3단계 이상이면 이렇게 가능할 것 같습니다. 0->null 은 필요시 변경하시면 되겠구요.
select min(rownum - 1) keep (dense_rank first order by delim_pos) over (partition by parent_category) parent_seq,
rownum seq,
parent_category,
category
from (
select category,
substr(category, 1, instr(category, '/', -1) - 1) parent_category,
instr(category, '/', -1) delim_pos
from t
order by category
) v
order by seq;
-- recursive
select min(rownum - 1) keep (dense_rank first order by level) over (partition by parent_category) parent_seq,
rownum seq,
parent_category,
category
from (
select category,
substr(category, 1, instr(category, '/', -1) - 1) parent_category
from t
order by category
) v
start with parent_category is null
connect by prior category = parent_category
order by seq;
) 버튼을 클릭하여 작성 하시면 됩니다.