ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle树 sql

Oracle树 sql

原创 Linux操作系统 作者:coolhe 时间:2011-08-02 17:07:52 0 删除 编辑

假设有数据表结构如下,只有叶子节点有数据:

1id  parentId    name        amount
21               成本    
32    1          工资  
43    2          基本工资    1000 
54    2          奖金        200
65    1          保险        400

现在想统计处父节点合计数 ,如下:

1id      name       amount
21       成本       1600    //2 + 5
32       工资       1200    //3 + 4
43       基本工资   1000 
54       奖金       200
65       保险       400

使用CTE语法构建临时表如下:

1with tmp as (
2  select 1 as id , null as parentid , '成本' as name , null as amount from dual union all
3  select 2,1 , '工资', null from dual union all
4  select 3,2 , '基本工资', 1000 from dual union all
5  select 4,2 , '奖金' , 200 from dual union all
6  select 5,1 , '保险' , 400 from dual
7)
8select * from tmp;
1ID                     PARENTID               NAME     AMOUNT                
2---------------------- ---------------------- -------- ----------------------
31                                             成本                          
42                      1                      工资                          
53                      2                      基本工资  1000                 
64                      2                      奖金     200                   
75                      1                      保险     400

解法一:

1select root_id,root_name,sum(amount)
from (select connect_by_root(id) root_id,connect_by_root(name) root_name,amount from tmp where connect_by_isleaf=1 connectby prior id = parentid)
group by root_id,root_name
order by root_id;

解法二(使用内查询方式):

1select id,parentid,name,
  (select sum(amount) from tmp a start with a.id=b.id connect by prior a.id=a.parentid ) sum_sal 
from tmp b order by 1;

基本思路都是利用 connect by 子句自根节点/分支节点往叶子结点搜索,找出不同的根节点/分支节点到叶子节点的路径再求和,修改一下解法一的子查询并查看一下结果集:

1select connect_by_root(id) start_id,id leaf_id,amount 
from tmp where connect_by_isleaf=1 connect by prior id = parentid;
01  START_ID    LEAF_ID     AMOUNT
02---------- ---------- ----------
03         1          3       1000
04         1          4        200
05         1          5        400
06         2          3       1000
07         2          4        200
08         3          3       1000
09         4          4        200
10         5          5        400

start_id 就是开始查找(不是start with)的节点id,leaf_id就是叶子节点的id,可以看到id=1的节点,也就是根节点的值等于三个叶子节点的值的总和,叶子节点的值是明确的。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/3090/viewspace-703846/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-14

  • 博文量
    118
  • 访问量
    565475