17,086
社区成员
发帖
与我相关
我的任务
分享
select inst_no,
inst_lvl,
sup_inst_no,
(select sum(nvl(dbal, 0))
from tablet b
where connect_by_isleaf=1
start with b.inst_no = a.inst_no
connect by b.sup_inst_no = prior b.inst_no) dbal,
(select sum(nvl(cbal, 0))
from tablet b
where connect_by_isleaf=1
start with b.inst_no = a.inst_no
connect by b.sup_inst_no = prior b.inst_no) cbal
from tablet a;
SQL> select * from tablet;
INST_NO INST_LVL SUP_INST_NO DBAL CBAL
---------- ---------- ----------- ---------- ----------
0000 1
0001 2 0000
0002 2 0000
000101 3 0001
000102 4 000101 200 100
000201 3 0002 100 50
6 rows selected
SQL>
SQL> select inst_no,
2 inst_lvl,
3 sup_inst_no,
4 (select sum(nvl(dbal, 0))
5 from tablet b
6 start with b.inst_no = a.inst_no
7 connect by b.sup_inst_no = prior b.inst_no) dbal,
8 (select sum(nvl(cbal, 0))
9 from tablet b
10 start with b.inst_no = a.inst_no
11 connect by b.sup_inst_no = prior b.inst_no) cbal
12 from tablet a;
INST_NO INST_LVL SUP_INST_NO DBAL CBAL
---------- ---------- ----------- ---------- ----------
0000 1 300 150
0001 2 0000 200 100
0002 2 0000 100 50
000101 3 0001 200 100
000102 4 000101 200 100
000201 3 0002 100 50
6 rows selected
SQL>
--这个不就是你的结果吗?
select inst_no,
inst_lvl,
sup_inst_no,
(select sum(nvl(cbal, 0))
from tablet b
start with b.inst_no = a.inst_no
connect by b.sup_inst_no = prior b.inst_no) cbal
from tablet a;