有一个单位表，字段 nparentcorpid1 为上级单位 id，字段 nlevelid 记录此单位的级次。
我已经知道如何查单位 13 及其所有下级单位
- SQL code
select id, nparentcorpid1, nlevelid, 100 balance from client c start with id = 13connect by prior id = nparentcorpid1
结果：
- HTML code
ID NPARENTCORPID1 NLEVELID BALANCE13 10 2 10041 13 3 10043 13 3 10044 13 3 10045 13 3 10046 13 3 10047 13 3 10048 13 3 10050 13 3 100
以及金额的求和
- SQL code
select 13 rootid, SUM(100) balanceSum from client c start with id = 13connect by prior id = nparentcorpid1
结果为
- HTML code
ROOTID BALANCESUM13 900
我现在疑惑的是关于求和的时候，rootid可否从start with的条件里面获得，如果将起始条件放宽，比如
- SQL code
select id, nparentcorpid1, nlevelid, 100 balance from client c start with id in (13,14,15)connect by prior id = nparentcorpid1
是否能够只写出一个SQL,获取到这样的结果：
- HTML code
ROOTID BALANCESUM13 90014 20013 500
(不是通过程序指定三次参数执行三次查询的方式)
------解决方案--------------------
你问问这个帖的楼主:
http://topic.csdn.net/u/20110928/00/9c3d7fa4-cb77-44e0-b81d-bcc862cade6c.html
------解决方案--------------------
- SQL code
select empno, mgr, ename, sal,levelfrom scott.empstart 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 2select decode (level, 1, empno, mgr), sum(sal)from scott.empstart with empno in (7902, 7698)connect by prior empno = mgrgroup by decode (level, 1, empno, mgr);DECODE(LEVEL,1,EMPNO,MGR) SUM(SAL)------------------------- ---------- 7698 9400 7902 3800
------解决方案--------------------
用LEVEL，树深度来判断
------解决方案--------------------
- SQL code
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 start with ilevel = 2 connect by prior id = mgrid) t group by id; ID BLANCE---------- ---------- 13 400 14 300