오라클 계층구조 쿼리 관련 0 3 235

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]

PRIOR 를 select 절에 쓸수있었네요. 처음 봤네요.

완전 편하게 쓸수있겠어요. 

http://www.gurubee.net/lecture/1300

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입