Oracle中start with xx connect by prior 语句解析

Oracle中start with xx connect by prior 语句解析

​ Oracle这种的start with语句主要对B型树的数据进行递归查询.可以指定数据树上的任一节点,然后查找到它所有的子节点或者父节点.

​ 现在有如下图的数据:

1. 我们先想数据库插入数据,这里用到oracle的批量插入写法

   # 1 建表
   CREATE TABLE START_WITH (
   sub_levels VARCHAR2(255) NOT NULL ,
   super_levels VARCHAR2(255) DEFAULT ''  NULL 
   )
   NOCOMPRESS
   ;
   ​
   # 批量插入方法1
   INSERT INTO START_WITH(sub_levels,super_levels) 
                        select  '2','1' from dual
               union all select '3','1' from dual; 
   ​
   # 批量插入方法2
   INSERT ALL INTO  START_WITH(sub_levels,super_levels) 
                                       VALUES  ('4','2')
       INTO START_WITH VALUES  ('5','2')
       INTO START_WITH VALUES  ('6','3')
       INTO START_WITH VALUES  ('7','3')
       INTO START_WITH VALUES  ('8','4')
       INTO START_WITH VALUES  ('9','4') 
   select 1 from dual; 
   # 正常写法
   insert into START_WITH(sub_levels,super_levels) values ('10','8');

2. 使用start with 实例1 -->>查询指定节点下的所有子节点

   
   # 实例1 : 查询指定节点下的所有子节点
   select * from START_WITH
   start with sub_levels = '2'
   connect by prior sub_levels = super_levels
   order by super_levels,sub_levels;

   上面的查询结果如下:

   

   现在对start with的用法进行解析.

   2.1 start with sub_levels = '2' 这段sql相当进行一个查询 select * from START_WITH where sub_levels = '2' ;

   我们能得到

   2.2 接着看connect by prior sub_levels = super_levels 这段sql 其中的prior sub_levels 表示将2.1中结果的sub_levels作为现在的值,此时这句sql相当于

   ​ connect by 2 = super_levels 这句sql我们可以看做是一个新的查询

   ​ select * from START_WITH super_levels = 2

   ​ 我们能得到结果

   ​ 接着程序会对我么sub_levels 为4的情况执行start with语句和connect by 语句,依次递归直到最后一个子级为止.

   2.3 我们可以将整个查询过程看做是如下流程

   ​

3. 使用start with 实例2 : -->> 查询指定节点下的所有父节点

   
   # 实例2 : 查询指定节点下的所有父节点
   select * from START_WITH 
   start with sub_levels = '8'
   connect by prior super_levels = sub_levels
   order by super_levels,sub_levels;

   ​ 结果如下:

   

   查询过程相当于:

4. 其余两查询

   
   select * from START_WITH 
   start with super_levels = '2'
   connect by prior sub_levels = super_levels
   order by super_levels,sub_levels;

   

select * from START_WITH 
start with super_levels = '8'
connect by prior  super_levels =  sub_levels
order by super_levels,sub_levels;

5. 总结

   通过上面的结果我们可以发现如下规律

   a. 当在prior 后跟子级时是自上而下的查询所有的子节点

   b. 当在prior后更父级时是自下而上的查询所有的父节点