oracle 递归分组统计
累加,比如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
现在遇到了效率问题。如果统计的列比较多,会有很多个 递归的子查询,查询速度非常慢。
如何优化处理呢?