oracle 中 start with ...connect by 的用法

2019独角兽企业重金招聘Python工程师标准>>>

 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。

 

转载于:https://my.oschina.net/u/2552902/blog/543883