순환쿼리 질문 0 3 982

by 김명제 순환쿼리 [2018.01.12 16:15:31]


순환.xls (24,064Bytes)

순환쿼리 관련 질문입니다.

현재 작업쿼리를 보면 ROW 생성은 된거 같은데 순서가 제대로 안잡힙니다.

원하는 순서는 엑셀로 업로드 하였습니다.

많은 의견 부탁드립니다

* LEV 낮은 순으로 MAX_USE 만큼 반복 ROW를 생성

* 상위로 올라가면 하위LEV 도 같이 반복

* 기초 데이터 -> TEMP_T

* 작업중인 쿼리

with temp_t as
 (select '10000' UIDcol, 'AAA' COD_ID, '' UID_MGR, 1 max_use
    from dual
  UNION ALL
  select '10001' UIDcol, 'ABC' COD_ID, '10000' UID_MGR, 1 max_use
    from dual
  UNION ALL
  select '10002' UIDcol, 'ABD' COD_ID, '10000' UID_MGR, 3 max_use
    from dual
  UNION ALL
  select '10003' UIDcol, 'ACC' COD_ID, '10000' UID_MGR, 2 max_use
    from dual
  UNION ALL
  select '10004' UIDcol, 'ACA' COD_ID, '10000' UID_MGR, 1 max_use
    from dual
  UNION ALL
  select '10005' UIDcol, 'ADC' COD_ID, '10000' UID_MGR, 1 max_use
    from dual
  UNION ALL
  select '10006' UIDcol, 'AFC' COD_ID, '10000' UID_MGR, 3 max_use
    from dual
  UNION ALL
  select '10007' UIDcol, 'ACG' COD_ID, '10006' UID_MGR, 1 max_use
    from dual
  UNION ALL
  select '10008' UIDcol, 'ACA' COD_ID, '10006' UID_MGR, 3 max_use
    from dual
  UNION ALL
  select '10009' UIDcol, 'ABC' COD_ID, '10008' UID_MGR, 2 max_use
    from dual
  UNION ALL
  select '10010' UIDcol, 'AfC' COD_ID, '10000' UID_MGR, 1 max_use
    from dual)
select * from (    
select a.*,
       TO_NUMBER(dbms_xmlgen.getxmltype('SELECT ' || con_val || ' FROM dual')
                 .Extract('//text()')) P_MAX_USE
  from (select LEVEL LEV,
               LPAD(' ', 4 * (LEVEL - 1)) || COD_ID UIDlev,
               UIDcol,
               UID_MGR,
               max_use,
               
               SUBSTR(SYS_CONNECT_BY_PATH(max_use, '*'), 2) con_val
          from temp_t
        
         start with UID_MGR is null
        connect by prior UIDcol = UID_MGR) a ) a ,  (select level row_seq from dual connect by level <= 99) b
where p_max_use >= row_seq

 

by 삐르짱 [2018.01.12 16:39:08]
with temp_t as
 (select '10000' UIDcol, 'AAA' COD_ID, '' UID_MGR, 1 max_use
    from dual
  UNION ALL
  select '10001' UIDcol, 'ABC' COD_ID, '10000' UID_MGR, 1 max_use
    from dual
  UNION ALL
  select '10002' UIDcol, 'ABD' COD_ID, '10000' UID_MGR, 3 max_use
    from dual
  UNION ALL
  select '10003' UIDcol, 'ACC' COD_ID, '10000' UID_MGR, 2 max_use
    from dual
  UNION ALL
  select '10004' UIDcol, 'ACA' COD_ID, '10000' UID_MGR, 1 max_use
    from dual
  UNION ALL
  select '10005' UIDcol, 'ADC' COD_ID, '10000' UID_MGR, 1 max_use
    from dual
  UNION ALL
  select '10006' UIDcol, 'AFC' COD_ID, '10000' UID_MGR, 3 max_use
    from dual
  UNION ALL
  select '10007' UIDcol, 'ACG' COD_ID, '10006' UID_MGR, 1 max_use
    from dual
  UNION ALL
  select '10008' UIDcol, 'ACA' COD_ID, '10006' UID_MGR, 3 max_use
    from dual
  UNION ALL
  select '10009' UIDcol, 'ABC' COD_ID, '10008' UID_MGR, 2 max_use
    from dual
  UNION ALL
  select '10010' UIDcol, 'AfC' COD_ID, '10000' UID_MGR, 1 max_use
    from dual) --select * from temp_t
SELECT UIDCOL
     , COD_ID
     , UID_MGR
     , MAX_USE
     , LEVEL
  FROM (SELECT *
          FROM TEMP_T A
             , (SELECT LEVEL AS LVL
                  FROM DUAL
               CONNECT BY LEVEL <= 3 
               ) B
         WHERE A.MAX_USE >= B.LVL  
         ORDER BY A.UIDCOL
                , B.LVL
       )
CONNECT BY PRIOR UIDCOL = UID_MGR
 START WITH UIDCOL = '10000'

 


by 김명제 [2018.01.12 17:04:24]

답변 감사드립니다. 많은 도움이 되었습니다 ^^


by 마농 [2018.01.15 09:02:20]
SELECT LEVEL lev
     , LPAD(' ', 4 * (LEVEL - 1)) || cod_id uidlev
     , uidcol
     , uid_mgr
     , max_use
  FROM temp_t a
     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 9) b
 WHERE b.lv <= a.max_use
 START WITH uid_mgr IS NULL
 CONNECT BY PRIOR uidcol = uid_mgr
 ORDER SIBLINGS BY uidcol, lv
;

 

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