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
上面语句查出来的应该是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
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;
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
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
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;