Oracle数据库 递归
0
悬赏园豆：5
[已解决问题]
解决于 2014-09-04 09:13
建一张表
create table t1(id number,par_id number,fy number);
insert into t1 values(1,null,null);
insert into t1 values(2,1,null);
insert into t1 values(3,2,15.2);
insert into t1 values(4,2,16.5);
insert into t1 values(5,1,null);
insert into t1 values(6,5,null);
insert into t1 values(7,6,30.9);
insert into t1 values(8,6,20.4);
insert into t1 values(9,6,null);
insert into t1 values(10,9,20.1);
insert into t1 values(11,9,30.5);
insert into t1 values(12,5,29.6);
commit;
结果是：
ID PAR_ID FY
1
2 1
3 2 15.2
4 2 16.5
5 1
6 5
7 6 30.9
8 6 20.4
9 6
10 9 20.1
11 9 30.5
12 5 29.6
1
2 1
3 2 15.2
4 2 16.5
5 1
6 5
7 6 30.9
8 6 20.4
9 6
10 9 20.1
11 9 30.5
12 5 29.6
我想要的结果是，所有有子节点的父节点的fy字段值，等于其直接子节点的和，结果应该是这样：
ID PAR_ID FY
1 163.2
2 1 31.7
3 2 15.2
4 2 16.5
5 1 131.5
6 5 101.9
7 6 30.9
8 6 20.4
9 6 50.6
10 9 20.1
11 9 30.5
12 5 29.6
请问怎么办
最佳答案
1
下面的语句就可以得到你要的结果了
select b.id,b.par_id,a.fy
from (select id,sum(fy) fy
from (select CONNECT_BY_ROOT id id,fy
from t1
connect by prior id = par_id)
group by id) a
inner join t1 b on a.id=b.id
order by id
收获园豆：5
其他回答(3)
0
Oracle提供了递归函数。
select * from t1
start with id= 1
connect by prior par_id= id;
1
select id,par_id,
(select sum(fy) from t1
start with id=t.id
connect by prior id=par_id
) FY
from t1 t
去试试
可以的话，记得给分
@｀此刻/无解:
因为ID=1根节点FY为空，所有通过聚合函数sum计算的时候，需要把FY等于null的列转换成0，不然ID=1的这行FY会等于null，空
select id,par_id,
(select nvl(sum(fy),0) from t1
start with id=t.id
connect by prior id=par_id
) FY
from t1 t