你的问题还真的让我为难了，现在的解决方法是用了递归函数的。下面是在
ORACLE9iR2上调试通过的。
create table test_department(
id number(4) not null,
d_name varchar2(40) not null,
parent_id number(4),
constraint pk_test_department primary key(id)
);
create table test_department_power(
id number(4) not null,
power number(4),
constraint pk_test_department_power primary key(id,power)
);
insert into test_department( id, d_name, parent_id) values(10, '部门1',null);
insert into test_department( id, d_name, parent_id) values(20, '部门2',10);
insert into test_department( id, d_name, parent_id) values(30, '部门3',10);
insert into test_department( id, d_name, parent_id) values(40, '部门4',10);
insert into test_department( id, d_name, parent_id) values(50, '部门5',20);
insert into test_department( id, d_name, parent_id) values(60, '部门6',20);
insert into test_department( id, d_name, parent_id) values(70, '部门7',30);
insert into test_department( id, d_name, parent_id) values(80, '部门8',70);
insert into test_department_power(id ,power) values( 10 , 1);
insert into test_department_power(id ,power) values( 20 , 2);
insert into test_department_power(id ,power) values( 30 , 3);
insert into test_department_power(id ,power) values( 40 , 4);
insert into test_department_power(id ,power) values( 50 , 5);
insert into test_department_power(id ,power) values( 60 , 6);
insert into test_department_power(id ,power) values( 70 , 7);
insert into test_department_power(id ,power) values( 80 , 8);
commit;
create or replace function qid2power(vid number , vlevel number)
return number is
pid number(4);
begin
if vlevel = 1 then
return vid;
end if;
select PARENT_ID into pid from test_department where id = vid;
return qid2power(pid , vlevel-1);
end;
/
create or replace view test_power_view as select b.id , a.power
from test_department_power a,
(select id , qid2power(id,level) pid
from test_department
connect by PRIOR id = PARENT_ID) b
where a.id = b.pid
order by b.id , a.power;
/
commit;
