꿈꾸는 개발자, DBA 커뮤니티 구루비

오라클 계층구조 쿼리 관련

by 빵덕엄마 [SQL Query] [2018.10.11 20:17:53]

as-is

상위코드	코드
-	c001
c001	a
c001	b
a	가
b	나
-	c002
c002	z
c002	y

to-be

순번	상위순번	코드
1	0	c001
2	0	c002
3	1	a
4	1	b
5	3	가
6	3	나
7	2	z
8	2	y

계층구조의 as-is 데이터를 to-be형태로 변경하고 싶습니다.

단순하게..짜보았는데..

with tab as 
(
select null as code_id, 'c001' code from dual union all
select 'c001' as code_id, 'a' code from dual union all
select 'c001' as code_id, 'b' code from dual union all
select 'a' as code_id, '가' code from dual union all
select 'a' as code_id, '나' code from dual union all
select null as code_id, 'c002' code from dual union all
select 'c002' as code_id, 'z' code from dual union all
select 'c002' as code_id, 'y' code from dual 
)
select a.sn, nvl(b.sn,'0') as upper_sn, a.code
  from (
         SELECT code_id, code, rownum sn
           FROM tab 
          start with code_id is null
          connect by prior code = code_id
       ) a, 
       (
         SELECT code_id, code, rownum sn
           FROM tab 
          start with code_id is null
          connect by prior code = code_id
       ) b 
 where a.code_id = b.code(+)
 order by a.sn;

이 방법으로 했을때...

with tab as 
(
select null as code_id, 'c001' code from dual union all
select 'c001' as code_id, 'a' code from dual union all
select 'c001' as code_id, 'b' code from dual union all
select 'a' as code_id, '가' code from dual union all
select 'a' as code_id, '나' code from dual union all
select null as code_id, 'c002' code from dual union all
select 'c002' as code_id, 'z' code from dual union all
select 'c002' as code_id, 'y' code from dual union all
select 'c001' as code_id, 'c002' code from dual -- code가 중복
)
select a.sn, nvl(b.sn,'0') as upper_sn, a.code
  from (
         SELECT code_id, code, rownum sn
           FROM tab 
          start with code_id is null
          connect by prior code = code_id
       ) a, 
       (
         SELECT code_id, code, rownum sn
           FROM tab 
          start with code_id is null
          connect by prior code = code_id
       ) b 
 where a.code_id = b.code(+)
 order by a.sn;

주석단 데이터처럼 코드가 중복인 자료는.. 순번오류가 발생하네요 ㅠ

혹시 다른 좋은 방법 있을까요?

by 마농 [2018.10.11 22:01:18]

WITH tab AS
(
SELECT null code_id, 'c001' code FROM dual
UNION ALL SELECT 'c001', 'a'    FROM dual
UNION ALL SELECT 'c001', 'b'    FROM dual
UNION ALL SELECT 'a'   , '가'   FROM dual
UNION ALL SELECT 'b'   , '나'   FROM dual
UNION ALL SELECT null  , 'c002' FROM dual  -- 중복 code
UNION ALL SELECT 'c002', 'z'    FROM dual
UNION ALL SELECT 'c002', 'y'    FROM dual
UNION ALL SELECT 'c001', 'c002' FROM dual  -- 중복 code
)
SELECT DISTINCT sn
     , NVL(PRIOR sn, 0) upper_sn
     , code
  FROM (SELECT code_id
             , code
             , DENSE_RANK() OVER(ORDER BY code) sn
          FROM tab
        )
 START WITH code_id IS NULL
 CONNECT BY PRIOR code = code_id
;

by 빵덕엄마 [2018.10.17 17:23:13]

마농님 감사합니다!!!!

by 우리집아찌 [2018.10.12 13:14:07]