父 子
-------
A A1
A A2
A1 A11
A1 A12
A11 A111
A2 A21
A2 A22
=====
表结构如上，如果通过一句oracel sql,期望选出结果如下
也就是深度搜索
父 子
-------
A A1
A1 A11
A11 A111
A1 A12
A A2
A2 A21
A2 A22
------解决方案--------------------
SELECT * FROM T递归查询
START WITH FU='A'
CONNECT BY PRIOR ZI = FU;
------解决方案--------------------
WITH TEST AS
(SELECT 'A' ID, 'A1' PID
FROM DUAL
UNION ALL
SELECT 'A' ID, 'A2' PID
FROM DUAL
UNION ALL
SELECT 'A1' ID, 'A11' PID
FROM DUAL
UNION ALL
SELECT 'A1' ID, 'A12' PID
FROM DUAL
UNION ALL
SELECT 'A11' ID, 'A111' PID
FROM DUAL
UNION ALL
SELECT 'A2' ID, 'A21' PID
FROM DUAL
UNION ALL
SELECT 'A2' ID, 'A22' PID
FROM DUAL)
SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(RN, '-')), '-') COL, T2.ID, T2.PID
FROM (SELECT T1.*,
ROW_NUMBER() OVER(PARTITION BY C_LEVEL, ID ORDER BY PID) AS RN
FROM (SELECT T.ID, T.PID, MAX(LEVEL) C_LEVEL
FROM TEST T
CONNECT BY ID = PRIOR PID
GROUP BY T.ID, T.PID) T1) T2
START WITH C_LEVEL = 1
CONNECT BY ID = PRIOR PID
GROUP BY T2.ID, T2.PID, T2.RN
ORDER BY LTRIM(MAX(SYS_CONNECT_BY_PATH(RN, '-')), '-')
;
------解决方案--------------------
SQL> WITH TEST AS (SELECT 'A' ID, 'A1' PID FROM DUAL
2 UNION ALL SELECT 'A' ID, 'A2' PID FROM DUAL
3 UNION ALL SELECT 'A1' ID, 'A11' PID FROM DUAL
4 UNION ALL SELECT 'A1' ID, 'A12' PID FROM DUAL
5 UNION ALL SELECT 'A11' ID, 'A111' PID FROM DUAL
6 UNION ALL SELECT 'A2' ID, 'A21' PID FROM DUAL
7 UNION ALL SELECT 'A2' ID, 'A22' PID FROM DUAL)
8 select * from test start with id='A' connect by prior pid=id;
ID PID
--- ----
A A1
A1 A11
A11 A111
A1 A12
A A2
A2 A21
A2 A22