oracle 递归查询问题

Question

现在有一个递归树 数据如下：id  mingzi  pid  
1   中国 
2   福建    1
3   浙江    1
4   三明    2
5   福州    2
6   杭州    3
7   绍兴    4现在要求显示如下  
id  mingzi  pid  ppid
4  三明     2    1
5  福州     2    1
6  绍兴     3    1
7  杭州     3    1也就是说再查询的时候 ，需要把根节点的id显示在查询中， sql语句如何写？

Answer 1

数据写错了吧，绍兴的pid应该是3（浙江）吧
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

Answer 2

--抛砖引玉
[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>

Answer 3

同疑问，楼主，举例数据和结果数据都写错了吧，6是杭州，7是绍兴，且绍兴的pid应该是3

Answer 4

不好意思， 是我写错了， 绍兴的pid应该是3

Answer 5

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

Answer 6

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
中国>福建>三明
中国>福建>福州
中国>浙江>杭州
中国>浙江>绍兴

Answer 7

connect_by_root 得到的是最根节点， 如果有三层的话，得到的也是最根
如果要是需要全部显示到一条记录里面，只能拼串sys_connect_by_path

Answer 8

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这个函数还蛮使用的