--抛砖引玉
[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 NULL
CONNECT BY PRIOR t.dept_id = t.parent_id