【请教一个递归汇总SQL】

[garfieldzf8]

基础数据表A
ID Name PID
1 A 0
2 B 0
3 C 0
4 D 3
5 E 3
------------------------------------------------------------------------------------
录入数据表B（A_ID为A表ID外键，录入数据时录入到子级）
GUID A_ID NUM
7328EFFEF1884058B2C1F9F2C6597A9E 1 100
48D6E9B573AA4B85B2AC6DC58096544A 2 100
D90C2296DABE4B67BCE15747069BA229 4 100
9DB6827724724D90B0F3FE12CFA587B2 5 100
-----------------------------------------------------------------------------------
想得到以下汇总结果，也就是把子节点数据汇总到父节点来。
A 100
B 100
C 200

谢谢。

[bw555]

WITH T AS (
select ID,NAME,CONNECT_BY_ROOT(ID) SID
from A
CONNECT BY PRIOR PID=ID)
SELECT MAX(T.NAME),SUM(B.NUM)
FROM T,B
WHERE T.SID=B.A_ID
GROUP BY T.ID

[bw555]

上面语句查出来的应该是5条记录，增加条件，结果集中只要根节点数据

WITH T AS (
select ID,NAME,CONNECT_BY_ROOT(ID) SID
from A
WHERE CONNECT_BY_ISLEAF=1
CONNECT BY PRIOR PID=ID)
SELECT MAX(T.NAME),SUM(B.NUM)
FROM T,B
WHERE T.SID=B.A_ID
GROUP BY T.ID

[小灰狼W]

select id, name, sum(num) num from (select a.id, a.name, connect_by_root(b.num) num from a, b where a.id = b.a_id(+) and connect_by_isleaf = 1 start with b.a_id is not null connect by prior a.pid = a.id) group by id, name;

[garfieldzf8]

多谢二位版主帮助。