Oracle樹 sql

coolhe發表於2011-08-02
OracleSQL

假設有資料表結構如下，只有葉子節點有資料：

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
<embed>
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

start_id 就是開始查詢（不是start with）的節點id，leaf_id就是葉子節點的id，可以看到id=1的節點，也就是根節點的值等於三個葉子節點的值的總和，葉子節點的值是明確的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/3090/viewspace-703846/，如需轉載，請註明出處，否則將追究法律責任。

相關文章