sql - Oracle 中的分层查询

Question

我在 Oracle11g 中有一个结构类似于 (id, parent_id) 的表。

id    parent_id
---------------
1     (null)
2     (null)
3     1
4     3
5     3
6     2
7     2

我想查询它以获取分层链接到每个 id 的所有行，因此结果应该是:

root_id  id    parent_id
------------------------
1        3     1
1        4     3
1        5     3
2        6     2
2        7     2
3        4     3
3        5     3

我一直在努力使用 connect by 和 start with 很长一段时间了，我所能得到的只是我想要的查询结果的一小部分喜欢:

select connect_by_root(id) root_id, id, parent_id from my-table
start with id=1
connect by prior id = parent_id

我不想使用任何 for 循环来获得我的完整结果。

有什么想法吗？

最好的问候，杰罗姆·勒弗雷尔

PS:在第一个答案后编辑，注意到我忘记了一些我想要的结果......

Answer 1

您发布的查询缺少 from 子句并且在 connect_by_root 中留下了下划线，但我假设这些实际上不是您问题的根源。

以下查询可为您提供所需的结果:

select * from (
   select connect_by_root(id) root_id, id, parent_id
   from test1
   start with parent_id is null
   connect by prior id = parent_id)
where root_id <> id

核心问题是您要指定一个特定的起始值，而不是指定一种识别根行的方法。将 id = 1 更改为 parent_id is null 允许返回表的全部内容。

我还添加了外部查询以从结果集中过滤出根行，这在您的问题中未提及，但显示在您想要的结果中。

SQL Fiddle Example

---

评论回复:

在提供的版本中，您确实获得了 id = 3 的后代，但不是以 3 为根的方式。这是因为我们从绝对根开始。解决这个问题很简单，只需省略 start with 子句:

SELECT *
FROM
  (SELECT connect_by_root(id) root_id,
          id,
          parent_id
   FROM test1
CONNECT BY
   PRIOR id = parent_id)
WHERE root_id <> id

SQL Fiddle Example