是要累加對吧，比如id=1下面包含所有的子節點和自身統計，也就是統計每個子樹和，根不要就用WHERE CONNECT_BY_ISLEAF=1過濾
SQL> SELECT id,p_id,value,
2 ( SELECT SUM(value) FROM
3 test_tree a
4 START WITH a.id=b.id
5 CONNECT BY PRIOR a.id=a.p_id
6 ) sum_sal
7 FROM
8 test_tree b;
ID P_ID VALUE SUM_SAL
---------- ---------- ---------- ----------
1 0 3 36
2 1 6 23
3 1 5 10
4 2 2 2
5 2 7 7
6 2 8 8
7 3 5 5
7 rows selected
SQL>
SQL> SELECT root_id,SUM(value)
2 FROM (select CONNECT_BY_ROOT a.id root_id,p_id,value
3 from test_tree a
4 CONNECT BY PRIOR id = a.p_id
5 )
6 GROUP BY root_id;
ROOT_ID SUM(VALUE)
---------- ----------
1 36
2 23
3 10
4 2
5 7
6 8
7 5
7 rows selected