工作中經常會遇到将資料庫中的資料以樹的形式展現的需求。以下我們來看一下該需求在Oracle中如何實作。
首先我們需要有一個樹形的表結構(當然有時候會出現表結構不是典型的樹形結構,而是多表存儲,需要根據多表連接配接查詢生成樹)
一、樹型表結構:
節點ID 上級ID 節點名稱
二、用法:
select 節點ID,節點名稱,level
from 表名
connect by prior 節點ID=上級節點ID
start with 上級節點ID=節點值
說明:
1、常見的樹形結構為公司組織機構、地區……
2、求節點ID以上的結構,或以下的結構,将“節點ID=上級節點ID”左右順序換一下即可。
3、Level為Oracle的特殊字段,表示“層”的意思。目前節點ID的下一層節點為“1”。
測試SQL: 1,建立表結構
create table Dept(
DepartNO varchar2 ( 10 ),
DepartName varchar2 ( 20 ),
TopNo varchar2 ( 10 )
);
插入資料:
insert into Dept values('001',' 董事會','0');
commit;
insert into Dept values('002','總裁辦 ','001');
commit;
insert into Dept values('003','财務部 ','001');
commit;
insert into Dept values('004','市場部 ','002');
commit;
insert into Dept values('005','公關部 ','002');
commit;
insert into Dept values('006','銷售部 ','002');
commit;
insert into Dept values('007','分銷處 ','006');
commit;
insert into Dept values('008','業務拓展處','004');
commit;
insert into Dept values('009','銷售科','007');
commit;
1,向前查 (從查詢本身一直到最上面的機構)
比如:
select distinct departno,departname, level
from dept
connect by prior topno = departno
start with
departno = ' 005 ' ;
2,向後查:(從查詢本身一直到最下面的機構)
select distinct departno,departname, level
from dept
connect by prior departno = topno
start with
topno = ' 001 ' ;
上面語句中用PRIOR表示上一條記錄,比如 CONNECT BY PRIOR ID=PRAENTID就是說上一條記錄的ID(比如根記錄)是(下一條)本條記錄的PRAENTID,即本記錄的父親是上一條記錄。
多表查詢例子:
select a.ar_file_name,a.ar_depart_id,b.depart_no,b.depart_name
from t_archives a left join csr_department b on(a.ar_depart_id = b.depart_no)
where a.ar_depart_id in(
select a.depart_no
from csr_department a
start with a.depart_no in ('8637')
connect by prior a.depart_id = a.parent_id
)