select COL_M,COL_S from
(SELECT DECODE(COL_M,COL_S,'XX',COL_M) AS COL_M,COL_S FROM TEMPKEN)
start with COL_M = '011'
connect by prior COL_S = COL_M
UNION
select COL_M,COL_S from
(SELECT DECODE(COL_M,COL_S,'XX',COL_M) AS COL_M,COL_S FROM TEMPKEN)
start with COL_S = '011'
connect by prior COL_M = COL_S
原数据:
----------------------------------------------------------
COL_M COL_S COL_REMARK
001 011 001-011
001 012 001-012
011 013 001-011-013
012 014 001-011-014
012 015 001-012-015
012 016 001-012-016
013 017 001-011-013-017
013 018 001-011-013-018
014 019 001-011-014-019
014 020 001-011-014-020
015 021 001-012-015-021
015 022 001-012-015-022
016 023 001-012-016-023
016 024 001-012-016-024
002 X11 002-X11
002 X12 002-X12
X11 X13 002-X11-X13
X11 X14 002-X11-X14
X12 X15 002-X12-X15
X12 X16 002-X12-X16
结果:
-------------------------------------------------
COL_M COL_S
014 019
014 020
XX 014
(SELECT DECODE(COL_M,COL_S,'XX',COL_M) AS COL_M,COL_S FROM TEMPKEN)
start with COL_M = '011'
connect by prior COL_S = COL_M
UNION
select COL_M,COL_S from
(SELECT DECODE(COL_M,COL_S,'XX',COL_M) AS COL_M,COL_S FROM TEMPKEN)
start with COL_S = '011'
connect by prior COL_M = COL_S
原数据:
----------------------------------------------------------
COL_M COL_S COL_REMARK
001 011 001-011
001 012 001-012
011 013 001-011-013
012 014 001-011-014
012 015 001-012-015
012 016 001-012-016
013 017 001-011-013-017
013 018 001-011-013-018
014 019 001-011-014-019
014 020 001-011-014-020
015 021 001-012-015-021
015 022 001-012-015-022
016 023 001-012-016-023
016 024 001-012-016-024
002 X11 002-X11
002 X12 002-X12
X11 X13 002-X11-X13
X11 X14 002-X11-X14
X12 X15 002-X12-X15
X12 X16 002-X12-X16
结果:
-------------------------------------------------
COL_M COL_S
014 019
014 020
XX 014