日期:2014-05-16 浏览次数:20808 次
select id, nparentcorpid1, nlevelid, 100 balance from client c start with id = 13 connect by prior id = nparentcorpid1
ID NPARENTCORPID1 NLEVELID BALANCE 13 10 2 100 41 13 3 100 43 13 3 100 44 13 3 100 45 13 3 100 46 13 3 100 47 13 3 100 48 13 3 100 50 13 3 100
select 13 rootid, SUM(100) balanceSum from client c start with id = 13 connect by prior id = nparentcorpid1
ROOTID BALANCESUM 13 900
select id, nparentcorpid1, nlevelid, 100 balance from client c start with id in (13,14,15) connect by prior id = nparentcorpid1
ROOTID BALANCESUM 13 900 14 200 13 500
select empno, mgr, ename, sal,level from scott.emp start with empno in (7902, 7698) connect by prior empno = mgr; EMPNO MGR ENAME SAL LEVEL ---------- ---------- ---------- ---------- ---------- 7902 7566 FORD 3000 1 7369 7902 SMITH 800 2 7698 7839 BLAKE 2850 1 7499 7698 ALLEN 1600 2 7521 7698 WARD 1250 2 7654 7698 MARTIN 1250 2 7844 7698 TURNER 1500 2 7900 7698 JAMES 950 2 select decode (level, 1, empno, mgr), sum(sal) from scott.emp start with empno in (7902, 7698) connect by prior empno = mgr group by decode (level, 1, empno, mgr); DECODE(LEVEL,1,EMPNO,MGR) SUM(SAL) ------------------------- ---------- 7698 9400 7902 3800
------解决方案--------------------
用LEVEL,树深度来判断
------解决方案--------------------
with tbl as ( select 13 as id, 10 as mgrid, 2 as ilevel, 100 as blance from dual union all select 41 as id, 13 as mgrid, 3 as ilevel, 100 as blance from dual union all select 42 as id, 13 as mgrid, 3 as ilevel, 100 as blance from dual union all select 43 as id, 13 as mgrid, 3 as ilevel, 100 as blance from dual union all select 14 as id, 10 as mgrid, 2 as ilevel, 100 as blance from dual union all select 44 as id, 14 as mgrid, 3 as ilevel, 100 as blance from dual union all select 45 as id, 14 as mgrid, 3 as ilevel, 100 as blance from dual ) select id, sum(blance) as blance from (select connect_by_root id as id, blance from tbl