oracle 递归查询问题

[huangdh12]

现在有一个递归树 数据如下：

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

[hudingchen]

数据写错了吧，绍兴的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

[BenChiM888]

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

[BenChiM888]

题目领会错误，(⊙o⊙)…。。。。。

[Quote=引用 2 楼 的回复:]

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 AL……
[/Quote]

[jdsnhan]

[Quote=引用 1 楼 的回复:]

数据写错了吧，绍兴的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
[/Quote]

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

[huangdh12]

[Quote=引用 4 楼 的回复:]

引用 1 楼 的回复:

数据写错了吧，绍兴的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


同疑问，楼主，……
[/Quote]

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

[huangdh12]

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

[Kobayashi]

二楼牛。。。

[ershihaidao]

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


中国>福建>三明
中国>福建>福州
中国>浙江>杭州
中国>浙江>绍兴

[BenChiM888]

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

sys_connect_by_path


[Quote=引用 6 楼 的回复:]

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

[槑党--一缕风]

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

这个函数还蛮使用的