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.
评论回应:
在提供的版本中,您确实会获得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
这篇关于Oracle中的分层查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!