Oracle中的分层查询 [英] Hierarchical queries in Oracle

问题描述

在Oracle11g中，我有一个表，其结构类似于(id，parent_id).

I have a table with a structure like (id, parent_id) in Oracle11g.

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

我想对其进行查询，以获取与每个id层次结构链接的所有行，因此结果应为:

I'd like to query it to get all the lines that are hierarchically linked to each of these id, so the results should be :

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上苦苦挣扎了很长时间，而我所能获得的只是我想要的查询结果的一小部分:

I've been struggling with the connect by and start with for quite some time now, and all i can get is a fraction of the results i want with queries like :

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

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

I'd like to not use any for loop to get my complete results.

有什么想法吗?

最诚挚的问候， 杰罗姆·勒弗雷(JérômeLefrère)

Best regards, Jérôme Lefrère

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

PS : edited after first answer, noticing me i had forgotten some of the results i want...

推荐答案

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

The query you posted is missing the from clause and left an underscore out of connect_by_root, but I'll assume those aren't actually the source of your problem.

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

The following query gives you the result you're looking for:

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可以返回表的全部内容.

The central problem is that you were specifying a specific value to start from, rather that specifying a way to identify the root rows. Changing id = 1 to parent_id is null allows the entire contents of the table to be returned.

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

I also added the outer query to filter the root rows out of the result set, which wasn't mentioned in your question, but was shown in your desired result.

SQL小提琴示例

评论回应:

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

In the version provided, you do get descendants of id = 3, but not in such a way that 3 is the root. This is because we're starting at the absolute root. Resolving this is easy, just omit the start with clause:

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小提琴示例

这篇关于Oracle中的分层查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！