如何查询树形结构中的节点和根节点关系

[zheninchangjiang]

表结构如下：
create table (
a varchar(10),
aname varchar(10),
afather varchar(10)
)

我想要如下的结果：
a aname aroot arootname

应该怎么办

[zheninchangjiang]

up 2

[sten]

想了一分钟，没明白楼主你所说的问题
再说清楚点，大家才能帮你解决

[zheninchangjiang]

insert into tablename(a,aname,afather) values('1','1name','')
insert into tablename(a,aname,afather) values('11','11name','1')
insert into tablename(a,aname,afather) values('2','2name','1')
insert into tablename(a,aname,afather) values('12','12name','2')

要的结果为：
a aname aroot arootname
1 1name
11 11name 1 1name
2 2name 1 1name
12 12name 1 1name

[zealot_001]

这个递归函数应该能帮到你：

create table test (
a varchar(10),
aname varchar(10),
afather varchar(10)
)

insert into test values('1','a','');
insert into test values('2','b','3');
insert into test values('3','c','5');
insert into test values('4','d','6');
insert into test values('5','e','1');
insert into test values('6','f','5');
commit;

select * from test

create or replace function get_root(pa in varchar2) return varchar2 as
cursor cur_b(p in varchar2) is
select a, aname, afather from test where a = p;
t test%rowtype;
a varchar2(10);
b varchar2(10);
begin
open cur_b(pa);
fetch cur_b
into t;
a := t.afather;
b := t.a;
close cur_b;
if a is null then --假设根节点的afather为空
return b;
else
return get_root(a);
end if;
end get_root;

select a.a, a.aname, c.af, c.an
from test a,
(select b.a aa, a.a af, a.aname an
from test a, (select a, get_root(a) id from test) b
where a.a = b.id) c
where a.a = c.aa

[sten]

select a.a,a.aname,b.a aroot,b.aname arootname
from tablename a,tablename b where a.afather = b.a
union
select c.a,c.aname,null aroot,null arootname from tablename c where c.afather is null;

[zheninchangjiang]

UP on

[uynix]

我也没明白lz的问题。
感觉lz的描述就是sten(近视进士)那样就可以了，根本不需要递归。

如果需要递归的话，oracle有connect by 可以用!

[zheninchangjiang]

我所要的结果是这样：
A ANAME AF AN
---------- ---------- ---------- ----------
1 a 1 a
2 b 1 a
3 c 1 a
4 d 1 a
5 e 1 a
6 f 1 a

6 rows selected

而不是
1 a
2 b 3 c
3 c 5 e
4 d 6 f
5 e 1 a
6 f 5 e