oracle的connect by用法

start with: 指定起始节点的条件

connect by: 指定父子行的条件关系

prior: 查询父行的限定符，格式: prior column1 = column2 or column1 = prior column2 and … ，

nocycle: 若数据表中存在循环行，那么不添加此关键字会报错，添加关键字后，便不会报错，但循环的两行只会显示其中的第一条

循环行: 该行只有一个子行，而且子行又是该行的祖先行

connect_by_iscycle: 前置条件:在使用了nocycle之后才能使用此关键字，用于表示是否是循环行，0表示否，1 表示是

connect_by_isleaf: 是否是叶子节点，0表示否，1 表示是

level: level伪列,表示层级，值越小层级越高，level=1为层级最高节点

-- 创建表
create table A_TEST_EMPLOYEE(
       emp_id number(18),
       lead_id number(18),
       emp_name varchar2(200),
       salary number(10,2),
       dept_no varchar2(8)
);

-- 添加数据
insert into A_TEST_EMPLOYEE values('1',0,'king','1000000.00','001');
insert into A_TEST_EMPLOYEE values('2',1,'jack','50500.00','002');
insert into A_TEST_EMPLOYEE values('3',1,'arise','60000.00','003');
insert into A_TEST_EMPLOYEE values('4',2,'scott','30000.00','002');
insert into A_TEST_EMPLOYEE values('5',2,'tiger','25000.00','002');
insert into A_TEST_EMPLOYEE values('6',3,'wudde','23000.00','003');
insert into A_TEST_EMPLOYEE values('7',3,'joker','21000.00','003');commit;

关系树状图

表结构和信息如下

--connect by 测试
select t.*, t.rowid from A_TEST_EMPLOYEE t;

查询以emp_id为1开始的节点的所有直属节点

select emp_id,lead_id,emp_name,prior emp_name as lead_name,salary
     from A_TEST_EMPLOYEE
     start with  emp_id=1
     connect by prior emp_id =  lead_id 
     

等价于

select emp_id,lead_id,emp_name,prior emp_name as lead_name,salary
     from A_TEST_EMPLOYEE
     start with  lead_id=0
     connect by prior emp_id =  lead_id 

查询结果如下

以emp_id为6的所有祖先节点

select emp_id,lead_id,emp_name,salary
       from A_TEST_EMPLOYEE
       start with emp_id = 6
       connect by prior lead_id = emp_id

查询结果如下

查询一个节点的叔叔伯父节点

with t as (
       select  A_TEST_EMPLOYEE.*,prior emp_name,level le
       from A_TEST_EMPLOYEE 
       start with lead_id = 0
       connect by lead_id=prior emp_id
       )
       select t.*
       from t 
       left join t tt on tt.emp_id=6
       where t.le = (tt.le-1)
       and t.emp_id not in (tt.lead_id)  

查询结果如下

查询族兄

with t as (
            select A_TEST_EMPLOYEE.*,prior emp_name,level le
           from A_TEST_EMPLOYEE 
           start with lead_id=0
           connect by lead_id= prior emp_id
       )
       select t.*
       from t  t
       left join t tt on tt.emp_id=6
       where t.le=tt.le and t.emp_id<>6;

查询结果如下

level伪列的使用，格式化层级

select lpad(' ',level*3,'   ')||emp_name as name, emp_id,lead_id,salary,level 
from a_test_employee 
start with lead_id = 0 
connect by prior emp_id = lead_id

查询结果如下

connect_by_root 查找根节点

select connect_by_root emp_id as root_emp_id,connect_by_root emp_name as root_emp_name, lpad(' ',level*3,'   ')||emp_name as name, emp_id,lead_id,salary,level
from a_test_employee 
where 1=1
and salary > 50000
start with lead_id = 0 
connect by prior emp_id = lead_id

查询结果如下

connect_by_isleaf 是否是叶子节点

select ate.*,level,connect_by_isleaf 
from a_test_employee ate
where 1=1
start with lead_id = 0
connect by prior emp_id = lead_id

查询结果如下