Oracle树 sql

最新推荐文章于 2022-08-18 14:14:29 发布

cpard3852987

最新推荐文章于 2022-08-18 14:14:29 发布

阅读量79

点赞数

假设有数据表结构如下，只有叶子节点有数据：

 
    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;

view source
print?

 
    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