由于项目上报表需求，需要按地区统计每个地区产生的工单数，地区是有上下级关系的，比如省级的，需要将下面市级的也统计进去。
问题来了，
selectcount(*)num,xzqh from ywgl_xzqh
where level 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
7FROM
8test_tree b;
ID P_IDVALUESUM_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)
2FROM (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
)
6GROUP BY root_id;
ROOT_ID SUM(VALUE)
---------- ----------
1 36
2 23
3 10
4
2
5
7
6
8
7
5
7 rows selected
|
