데이터베이스 질문과 답변
with TMP_TABLE as
(
select 'A' as MATLOTID, 'AA' AS NEWVALUE from dual
union all
select 'A' as MATLOTID, 'AB' AS NEWVALUE from dual
union all
select 'A' as MATLOTID, 'AC' AS NEWVALUE from dual
union all
select 'AA' as MATLOTID, 'AA1' AS NEWVALUE from dual
union all
select 'AA1' as MATLOTID, 'AA11' AS NEWVALUE from dual
)
SELECT *
FROM TMP_TABLE
start with MATLOTID = 'A'
connect by nocycle prior NEWVALUE= MATLOTID
위 쿼리 결과.
| MATLOTID | NEWVALUE |
| A | AA |
| AA | AA1 |
| AA1 | AA11 |
| A | AB |
| A | AC |
[최종 결과]
| RESULT |
| A->AA->AA1->AA11 |
| A->AB |
| A->AC |
어떠한 방법으로 해야 결과값이 나올지 고수님들 부탁드립니다.ㅠ
by 부쉬맨
[2014.11.18 13:18:32]
with TMP_TABLE as
(
select 'A' as MATLOTID, 'AA' AS NEWVALUE from dual
union all
select 'A' as MATLOTID, 'AB' AS NEWVALUE from dual
union all
select 'A' as MATLOTID, 'AC' AS NEWVALUE from dual
union all
select 'AA' as MATLOTID, 'AA1' AS NEWVALUE from dual
union all
select 'AA1' as MATLOTID, 'AA11' AS NEWVALUE from dual
)
SELECT sys_connect_by_path(NEWVALUE,'>')
, sys_connect_by_path(MATLOTID,'>')
FROM TMP_TABLE
start with MATLOTID = 'A'
connect by nocycle prior NEWVALUE= MATLOTID
by DarkBee
[2014.11.18 13:25:43]
SELECT MAX ( path )
FROM (
SELECT a.*
, LEVEL lv
, CONNECT_BY_ROOT ( matlotid )
|| '->'
|| SUBSTR ( SYS_CONNECT_BY_PATH ( newvalue, '->' ), 3 ) path
FROM tmp_table a
START WITH matlotid = 'A'
CONNECT BY NOCYCLE PRIOR newvalue = matlotid
)
GROUP BY lv - ROWNUM
by 비주류
[2014.11.18 13:31:30]
SELECT CONNECT_BY_ROOT(matlotid) || SYS_CONNECT_BY_PATH(newvalue, '->') FROM tmp_table WHERE CONNECT_BY_ISLEAF = 1 START WITH matlotid = 'A' CONNECT BY NOCYCLE PRIOR newvalue = matlotid
by 김성수
[2014.11.18 14:01:31]
다양한 방법이 있네요 ^^
여러분 정말 고맙습니다.!!
) 버튼을 클릭하여 작성 하시면 됩니다.