有一个单位表,字段 nparentcorpid1 为上级单位 id,字段 nlevelid 记录此单位的级次。
我已经知道如何查单位 13 及其所有下级单位
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我现在疑惑的是关于求和的时候,rootid可否从start with的条件里面获得,如果将起始条件放宽,比如
select id, nparentcorpid1, nlevelid, 100 balance
from client c
start with id in (13,14,15)
connect by prior id = nparentcorpid1
是否能够只写出一个SQL,获取到这样的结果:ROOTID BALANCESUM
13 900
14 200
13 500
(不是通过程序指定三次参数执行三次查询的方式)
我已经知道如何查单位 13 及其所有下级单位
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我现在疑惑的是关于求和的时候,rootid可否从start with的条件里面获得,如果将起始条件放宽,比如
select id, nparentcorpid1, nlevelid, 100 balance
from client c
start with id in (13,14,15)
connect by prior id = nparentcorpid1
是否能够只写出一个SQL,获取到这样的结果: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 2select 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
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