oracle 递归查询有关问题

--抛砖引玉[SYS@myoracle] SQL>WITH T1 AS  2   (SELECT 1 ID, '中国' MINGZI, NULL PID  3      FROM DUAL  4    UNION ALL  5    SELECT 2 ID, '福建' MINGZI, 1 PID  6      FROM DUAL  7    UNION ALL  8    SELECT 3 ID, '浙江' MINGZI, 1 PID  9      FROM DUAL 10    UNION ALL 11    SELECT 4 ID, '三明' MINGZI, 2 PID 12      FROM DUAL 13    UNION ALL 14    SELECT 5 ID, '福州' MINGZI, 2 PID 15      FROM DUAL 16    UNION ALL 17    SELECT 6 ID, '杭州' MINGZI, 3 PID 18      FROM DUAL 19    UNION ALL 20    SELECT 7 ID, '绍兴' MINGZI, 3 PID FROM DUAL), 21  T2 AS 22   (SELECT ID, 23           MINGZI, 24           PID, 25           CONNECT_BY_ROOT ID IID, 26           RTRIM(LTRIM(SYS_CONNECT_BY_PATH(PID, ','), ','), ',') P 27      FROM T1 28     START WITH ID IN (4, 5, 6, 7) 29    CONNECT BY ID = PRIOR PID), 30  T3 AS 31   (SELECT IID, MAX(P) P FROM T2 GROUP BY IID) 32  SELECT T1.ID, T1.MINGZI, T3.P FROM T1, T3 WHERE T1.ID = T3.IID ORDER BY ID 33  ;        ID MING P---------- ---- -----         4 三明 2,1         5 福州 2,1         6 杭州 3,1         7 绍兴 3,1[SYS@myoracle] SQL>
------解决方案--------------------
探讨


数据写错了吧，绍兴的pid应该是3（浙江）吧
SQL code
SELECT t.*,
       CONNECT_BY_ROOT(t.ID) ppid
  FROM tb t
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH t.pid IS NULL
CONNECT BY PRIOR t.ID = t.pid

------解决方案--------------------
WITH T AS
  (SELECT 1 ID, '中国' MINGZI, NULL PID
     FROM DUAL
   UNION ALL
   SELECT 2 ID, '福建' MINGZI, 1 PID
     FROM DUAL
   UNION ALL
   SELECT 3 ID, '浙江' MINGZI, 1 PID
     FROM DUAL
   UNION ALL
   SELECT 4 ID, '三明' MINGZI, 2 PID
     FROM DUAL
   UNION ALL
   SELECT 5 ID, '福州' MINGZI, 2 PID
     FROM DUAL
   UNION ALL
   SELECT 6 ID, '杭州' MINGZI, 3 PID
     FROM DUAL
   UNION ALL
   SELECT 7 ID, '绍兴' MINGZI, 3 PID FROM DUAL)
SELECT substr(sys_connect_by_path(MINGZI,'>'),2)
 FROM t
  WHERE CONNECT_BY_ISLEAF = 1
  START WITH t.pid IS NULL
CONNECT BY PRIOR t.ID = t.pid


中国>福建>三明
中国>福建>福州
中国>浙江>杭州
中国>浙江>绍兴
------解决方案--------------------
connect_by_root 得到的是最根节点， 如果有三层的话，得到的也是最根
如果要是需要全部显示到一条记录里面，只能拼串

sys_connect_by_path


探讨


2楼就是我要的答案， 谢谢。 
乘着这个发帖，正好询问一下， 像这种获取部门层级等 父子关系的方法，需要用到哪些函数？
除了start with connect by prior  
例如， 如何将 子内容和父内容显示在一条记录当中？

------解决方案--------------------
SQL code
SELECT t.dept_id,--id       t.dept_name,--name       SYS_CONNECT_BY_PATH(dept_name, '->') path1,       substr(sys_connect_by_path(dept_name, '>'), 2) path2,--path       CONNECT_BY_ROOT(t.dept_id) top_id --top  FROM t_com_dept_info t START WITH t.parent_id IS NULLCONNECT BY PRIOR t.dept_id = t.parent_id