树状查询是否可以获取到起始节点并分组解决办法-Oracle教程-爱易网页

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

日期：2014-05-16　浏览次数：20759 次

树状查询是否可以获取到起始节点并分组
有一个单位表，字段 nparentcorpid1 为上级单位 id，字段 nlevelid 记录此单位的级次。
我已经知道如何查单位 13 及其所有下级单位

SQL code

select id, nparentcorpid1, nlevelid, 100 balance
  from client c
 start with id = 13
connect by prior id = nparentcorpid1

结果：

HTML code


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

以及金额的求和

SQL code

select 13 rootid, SUM(100) balanceSum
  from client c
 start with id = 13
connect by prior id = nparentcorpid1

结果为

HTML code

ROOTID    BALANCESUM
13    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    BALANCESUM
13    900
14    200
13    500

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

------解决方案--------------------
你问问这个帖的楼主:

http://topic.csdn.net/u/20110928/00/9c3d7fa4-cb77-44e0-b81d-bcc862cade6c.html
------解决方案--------------------

SQL code


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，树深度来判断

------解决方案--------------------

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

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

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

相关资料更多>

推荐阅读更多>