一.应用场景
首先,connect by和level都是为了快速的查询层级关系的关键字,在代理关系中,或者权限关系中,经常会有层层嵌套的场景,比如,同行数据的第一个字段是ID,第二个字段是parentID,parentID表示他的上级ID是谁。
1.第一条数据,ID为1,parentID为0,表示没有上级,即顶层,grade 为老板
2.第二条数据,ID为2,parentID为1,表示上级为1,grade 为总监
3.第三条数据,ID为3,parentID为2,表示上级为2,grade 为经理
这就是一个三级的层级关系事例。
在这种情况下,如果想知道某个:比如 一级代理的所有二级代理和三级代理的有关行是谁,就很麻烦了。
二.语法解释
1 select * from table
2 start with org_id = ‘HBHqfWGWPy’
3 connect by prior org_id = parent_id;
简单说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段:org_id,parent_id,那么通过表示每一条记录的parent是谁,就可以形成一个树状结构,用上述语法的查询可以取得这棵树的所有记录,其中
条件1 是根结点的限定语句,当然可以放宽限定条件,以取得多个根结点,实际就是多棵树。
条件2 是连接条件,其中用PRIOR表示上一条记录,比如 CONNECT BY PRIOR org_id = parent_id;就是说上一条记录的org_id 是本条记录的parent_id,即本记录的父亲是上一条记录。
条件3 是过滤条件,用于对返回的所有记录进行过滤。
简单介绍如下:
在扫描树结构表时,需要依此访问树结构的每个节点,一个节点只能访问一次,其访问的步骤如下:
第一步:从根节点开始;
第二步:访问该节点;
第三步:判断该节点有无未被访问的子节点,若有,则转向它最左侧的未被访问的子节,并执行第二步,否则执行第四步;
第四步:若该节点为根节点,则访问完毕,否则执行第五步;
第五步:返回到该节点的父节点,并执行第三步骤。
总之:扫描整个树结构的过程也即是中序遍历树的过程。
三.使用小例子
SELECT
emp_id,
lead_id,
emp_name,
PRIOR emp_name "lead_name",
salary,
LEVEL
FROM
employee START WITH emp_id = 1 CONNECT BY PRIOR emp_id = lead_id
-- 创建表
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;