oracle 递归查询有关问题

oracle 递归查询问题
现在有一个递归树数据如下：

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语句如何写？

------解决方案--------------------

SQL code


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

相关推荐