oracle的分层查询（递归查询）

1.分层查询语句：

• start  with：指定层次的根部行，就是从哪里开始解析，对于真正的层次查询，（通俗解释：子句定义树查询的初始起点）这个子句是必需要有的。
• connect  by prior：解释父子之间的关系（通俗解析：子句定义表中的数据是如何相互联系的）
• prior：用于实现递归条件
• level关键字：表示查询深度(level 表示递归的层次)

2.建表

建立dept表

CREATE TABLE "DHEE"."DEPT" ( 
  "DEPT_ID" VARCHAR2(20)
  , "PARENT_ID" VARCHAR2(20)
  , "DEPT_NAME" VARCHAR2(20)
)

插入数据

insert into DEPT values ('100','0','若依科技');
insert into DEPT values ('101','100','深圳总公司');
insert into DEPT values ('102','100','长沙分公司');
insert into DEPT values ('103','101','研发部门');
insert into DEPT values ('104','101','市场部门');
insert into DEPT values ('105','101','测试部门');
insert into DEPT values ('106','101','财务部门');
insert into DEPT values ('107','101','运维部门');
insert into DEPT values ('108','102','市场部门');
insert into DEPT values ('109','102','财务部门');

查询数据后显示

3.分层查询

select
  parent_id
  , dept_id
  , dept_name 
from
  dept 
start with
  parent_id = '0' 
connect by
  prior dept_id = parent_id

PARENT_ID    DEPT_ID    DEPT_NAME
 0       100    若依科技
100    101    深圳总公司
101    103    研发部门
101    104    市场部门
101    105    测试部门
101    106    财务部门
101    107    运维部门
100    102    长沙分公司
102    108    市场部门
102    109    财务部门

4.分层查询问题总结

遍历树的方向：
进一步说明CONNECT BY子句，它决定你是从顶向下还是从底向上进行遍历。CONNECT BY PRIOR col_1 = col_2。

如果是从顶向下遍历：
test_parent（col_1） 是父键（它标识父），test_child(col_2) 是子键（它标识子）

select
  parent_id
  , dept_id
  , dept_name 
from
  dept 
start with
  parent_id = '100' 
connect by
  prior dept_id = parent_id

PARENT_ID    DEPT_ID    DEPT_NAME
100    101    深圳总公司
101    103    研发部门
101    104    市场部门
101    105    测试部门
101    106    财务部门
101    107    运维部门
100    102    长沙分公司
102    108    市场部门
102    109    财务部门

如果是从底向上遍历：
test_parent（col_1）就是子键，test_child（col_2） 就是父键了。

select
  parent_id
  , dept_id
  , dept_name 
from
  dept 
start with
  parent_id = '100' 
connect by
  prior parent_id = dept_id

PARENT_ID    DEPT_ID    DEPT_NAME
100    101    深圳总公司
0    100        若依科技
100    102    长沙分公司
0    100        若依科技
 

d.递归（树）查询level关键字

level解释：

level 和 connect by ，start with 合在一起，一般是用来实现递归（树）查询。
     connect by 子句定义表中的数据是如何相互联系的 
     start with 子句定义树查询的初始起点
     level 表示查询深度(level 表示递归的层次)

-- level 还可以用于图形化输出结果
--------lpad左填充用*
--------level表示查询深度（level表示递归的层次）

  select lpad(dept_id,length(dept_id) + level*10-10,'*') from dept 
       start with parent_id = '0' 
       connect by prior dept_id = parent_id;

LPAD(DEPT_ID,LENGTH(DEPT_ID)+LEVEL*10-10,'*')
100
**********101
********************103
********************104
********************105
********************106
********************107
**********102
********************108
********************109