– 添加数据 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;
数据列表展示:
数据树形展示:
(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
(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;
(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)
(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;
(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数值越低级别越高
(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;
(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;
(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;