树状查询是否可以获取到起始节点并分组

qinchun36 2011-09-28 11:47:54

有一个单位表，字段 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

(不是通过程序指定三次参数执行三次查询的方式)

...全文

220 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

opps_zhou 2011-09-28

打赏
举报



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

iqlife 2011-09-28

打赏
举报

用LEVEL，树深度来判断

ngx20080110 2011-09-28

打赏
举报



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