根据001002得到所有的路径

WITH a AS (SELECT '001' ID , 'a' NAME FROM dual
UNION ALL
SELECT '001002' ID , 'b' NAME FROM dual
UNION ALL
SELECT '001003' ID , 'c' NAME FROM dual
UNION ALL
SELECT '001002001' ID , 'd' NAME FROM dual
UNION ALL
SELECT '002' ID , 'f' NAME FROM dual
UNION ALL
SELECT '002001' ID , 'e' NAME FROM dual
)
SELECT ID,MIN(NAME ) FROM
(
SELECT ID, SYS_CONNECT_BY_PATH(D.name, '/') NAME FROM a d
CONNECT BY  prior d.id = substr(d.id, 0, length(d.id) - 3) )
c GROUP BY ID