欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle进阶教程之connectby实例学习

程序员文章站 2022-06-02 12:46:48
connect by 用于存在父子,祖孙,上下级等层级关系的数据表进行层级查询。 语法格式: { connect by [ nocycle ] condition [and condition].....

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

语法格式:

{ connect by [ nocycle ] condition [and condition]... [ start with condition ]

| start with condition connect by [ nocycle ] condition [and condition]...

}

特殊词讲解

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 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;

数据列表展示:

oracle进阶教程之connectby实例学习

数据树形展示:

oracle进阶教程之connectby实例学习

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

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

-- 等同于

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

oracle进阶教程之connectby实例学习

(2) 以emp_id为6的所有祖先节点

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

oracle进阶教程之connectby实例学习

(3) 查询一个节点的叔叔伯父节点

       with t as (
       select  employee.*,prior emp_name,level le
       from employee 
       start with lead_id = 0
       connect by lead_id=prior emp_id
       )
       select *
       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)    

oracle进阶教程之connectby实例学习

(4) 查询族兄

       with t 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 t  t
       left join t tt on tt.emp_id=6
       where t.le=tt.le and t.emp_id<>6;

oracle进阶教程之connectby实例学习

(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

oracle进阶教程之connectby实例学习

level数值越低级别越高

(6) connect_by_root 查找根节点

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

oracle进阶教程之connectby实例学习

(7) 标注循环行

       -- 插入一条数据,与另一条emp_id=7的数据组成循环行
       insert into employee values('3',7,'joker_cycle','21000.00','003');
       commit;
       -- connect_by_iscycle("cycle"), connect by nocycle
       select emp_id,emp_name,lead_id,salary,connect_by_iscycle as cycle 
       from employee 
       start with lead_id=0
       connect by nocycle prior emp_id = lead_id;

oracle进阶教程之connectby实例学习

(8) 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;

oracle进阶教程之connectby实例学习