Oracle connect by 案例详解

1、作用

用于存在父子，祖孙，上下级等层级关系的数据表进行层级查询。

2、语法

SELECT 字段1,字段2.....
FROM 表名
START WITH cond1 
CONNECT BY cond2
WHERE cond3;

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为层级最高节点

3、案例表结构 + 数据

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

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

commit;

 

3.1、查询jack和arise下的所有子节点

select * from employee start with emp_name='jack' connect by prior emp_id=lead_id;

 

3.2、查询jack的祖先节点

select * from employee start with emp_name='jack' connect by prior lead_id=emp_id;

 

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

--查看emp_id为6的节点的叔叔伯父节点
with temp as (
    select  employee.*,
            prior emp_name,
            level le
    from employee 
    start with lead_id = 0
    connect by lead_id=prior emp_id
)
select *
from temp t
left join temp tt 
    on tt.emp_id=6 --此处需要限定
where t.le = (tt.le-1)
    and t.emp_id not in (tt.lead_id);

 

 3.4、查询族兄

--查看employee id是6的节点的族兄节点
with temp as (
   select employee.*,
            prior emp_name,
            level le
   from employee 
   start with lead_id=0
   connect by lead_id= prior emp_id
)

select t.*
from temp  t
left outer join temp tt
 on tt.emp_id=6 --此处需要条件限制
where t.le=tt.le 
    and t.emp_id<>6; --此处需要条件限制 

 

 

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

select lpad(' ',level*2,' ')||emp_name as name,emp_id,lead_id,salary,level
from employee
start with lead_id=0
connect by prior emp_id=lead_id; -- level数值越低级别越高

 

 

3.6、connect_by_root 查找根节点

select connect_by_root emp_name,emp_name,lead_id,salary
from employee
start with lead_id=1
connect by prior emp_id = lead_id;

 

3.7、connect_by_isleaf 是否是叶子节点

select emp_id,emp_name,lead_id,salary,connect_by_isleaf
from employee
start with lead_id=0
connect by nocycle prior emp_id=lead_id;