oracle 中 start with …connect by 的用法

 connect by 是結構化查詢中用到的，其基本文法是：
select ... from tablename start with 條件1
connect by 條件2
where 條件3;
條件1 是根結點的限定語句，當然可以放寬限定條件，以取得多個根結點，實際就是多棵樹。
條件2 是串連條件，其中用PRIOR表示上一條記錄，比如 CONNECT BY PRIOR org_id = parent_id就是說上一條記錄的org_id 是本條記錄的parent_id，即本記錄的父親是上一條記錄。
條件3 是過濾條件，用於對返回的所有記錄進行過濾。

例子使用如下：

create table test(superid varchar2(20),id varchar2(20));

insert into test values('0','1');
insert into test values('0','2');

insert into test values('1','11');
insert into test values('1','12');

insert into test values('2','21');
insert into test values('2','22');

insert into test values('11','111');
insert into test values('11','112');

insert into test values('12','121');
insert into test values('12','122');

insert into test values('21','211');
insert into test values('21','212');

insert into test values('22','221');
insert into test values('22','222');

commit;

select level||'層',lpad(' ',level*5)||id id
from test
start with superid = '0' connect by prior id=superid;

LEVEL||'層'                                ID
------------------------------------------ --------------------------------------------------------------------------------
1層                                             1
2層                                                  11
3層                                                       111
3層                                                       112
2層                                                  12
3層                                                       121
3層                                                       122
1層                                             2
2層                                                  21
3層                                                       211
3層                                                       212
2層                                                  22
3層                                                       221
3層                                                       222

 

select level||'層',connect_by_isleaf,lpad(' ',level*5)||id id
from test
start with superid = '0' connect by prior id=superid;

LEVEL||'層'                                CONNECT_BY_ISLEAF ID
------------------------------------------ ----------------- --------------------------------------------------------------------------------
1層                                                        0      1
2層                                                        0           11
3層                                                        1                111
3層                                                        1                112
2層                                                        0           12
3層                                                        1                121
3層                                                        1                122
1層                                                        0      2
2層                                                        0           21
3層                                                        1                211
3層                                                        1                212
2層                                                        0           22
3層                                                        1                221
3層                                                        1                222

 

 

--connect_by_isleaf，判斷該節點是否為子節點。

--level,是一個虛列。配合connect by ...start with 使用。

--pad翻譯：填充

lpad函數，在字串的左側添加指定字串，用法：

lpad(String ,截取長度，添加的字串)。

說是添加字串也不準確，比較準確的說法是對String進行截取字串，如果截取長度大於String的長度，則在

String的左側添加字串進行填補，如果第三個參數未指定，則用空格進行填補。例如：

select lpad('test',10) from dual;

將返回“    test”

select lpad('test',10,'ee') from dual;

結果將返回eeeeeetest。