Oracle中Start with…Connect By理解及用法

Oracle中Start with...Connect By實現部門多級遞迴理解及用法

 

其基本文法是：

select ... from tablename start with cond1

connect by cond2

where cond3;

簡單說來是將一個樹狀結構儲存在一張表裡，比如一個表中存在兩個欄位: id,parentid,那麼通過表示每一條記錄的parent是誰，就可以形成一個樹狀結構。用上述文法的查詢可以取得這棵樹的所有記錄。

其中COND1是根結點的限定語句，當然可以放寬限定條件，以取得多個根結點，實際就是多棵樹。

COND2是串連條件，其中用PRIOR表示上一條記錄，比如 CONNECT BY PRIOR ID=PRAENTID就是說上一條記錄的ID是本條記錄的PRAENTID，即本記錄的父親是上一條記錄

COND3是過濾條件，用於對返回的所有記錄進行過濾

例子:

建立樣本表：

CREATE TABLE TBL_TEST

(

ID NUMBER,

NAME VARCHAR2(100 BYTE),

PID NUMBER DEFAULT 0

);

插入測試資料：

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');

INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

插入資料後：

 

Oracle中Start with...Connect By理解及用法

 

其基本文法是： select ... from tablename start with cond1 connect by cond2 where cond3; 簡單說來是將一個樹狀結構儲存在一張表裡，比如一個表中存在兩個欄位: id,parentid,那麼通過表示每一條記錄的parent是誰，就可以形成一個樹狀結構。用上述文法的查詢可以取得這棵樹的所有記錄。 其中COND1是根結點的限定語句，當然可以放寬限定條件，以取得多個根結點，實際就是多棵樹。 COND2是串連條件，其中用PRIOR表示上一條記錄，比如 CONNECT BY PRIOR ID=PRAENTID就是說上一條記錄的ID是本條記錄的PRAENTID，即本記錄的父親是上一條記錄。 COND3是過濾條件，用於對返回的所有記錄進行過濾。 例子: 建立樣本表： CREATE TABLE TBL_TEST ( ID NUMBER, NAME VARCHAR2(100 BYTE), PID NUMBER DEFAULT 0 ); 插入測試資料： INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0'); INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1'); INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0'); INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1'); INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2'); 插入資料後：   從Root往樹末梢遞迴(取所有點) select * from TBL_TEST start with id=1 connect by prior id = pid 顯示結果： 從末梢往樹ROOT遞迴(只取根結點,根結點的根結點......) select * from TBL_TEST start with id=5 connect by prior pid = id 結果：     註： 　prior 寫在哪個欄位那邊，那麼就用start with 這條記錄的該項值　與　別的記錄的另一個欄位的值去關聯; start with 可以不寫，預設為所有記錄 例如 select * from TBL_TEST start with id=5 connect by prior pid = id 因為pid=2 與id=5 在本資料表中為同一條資料， 因此可以理解為： select * from TBL_TEST start with pid=2 connect by prior pid = id (個人認為這樣更好理解)

 ****************************************************

此語句可以直接分層次顯示

SELECT lpad('|-',(level-1)*4,'|-')||lpad('『',2)||di_name||rpad('』',2)
di_name,di_id
FROM t_dictionary_link CONNECT BY PRIOR di_id=di_protypeid
START WITH di_id=-1

 

|-|-『公司架構』                                                                         2
|-|-|-|-『芯碼科技』                                                                     3
|-|-|-|-|-|-『開發部』                                                                   4
|-|-|-|-|-|-|-|-『JAVA組』                                                              11
|-|-|-|-|-|-|-|-『.NET組』                                                             102
|-|-|-|-|-|-『銷售部』                                                                 261
|-|-|-|-|-|-『客服部』