Connect By
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;
(1) 查询以lead_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 employe 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;
最后一行与新追加的一行是循环关系,因此connect_by_iscycle列显示值为1,但结果集只显示循环的第一条,与之循环的另外一条(新追加的一条)是不显示的
connect by 后面的nocycle关键字是防止出现父子关系循环的,如果表中出现父子关系循环且没有使用该关键字,会报如下错误:
--ora-01436: 用户数据中的 connect by 循环
(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;
叶节点指的是没有子节点的节点,那些是既是父节点又是子节点的节点不属于叶节点
推荐阅读
-
nginx connect() to unix:/var/run/php-fpm.sock failed (11: Resource temporarily unavailable)
-
关于mysql init_connect的几个要点总结
-
第七章 鼠标(CONNECT)
-
MySQL实现类似于connect_by_isleaf的功能MySQL方法或存储过程
-
Linux登录MySQL时出现 Can't connect to local MySQL server through socket '/tmp/mysql.sock'解决方法
-
oracle sys_connect_by_path 函数 结果集连接
-
Caused by SSLError("Can’t connect to HTTPS URL because the SSL module is not available)
-
怎么注册微软Bug提交报告或建议Connect账号?
-
react-redux中connect()方法详细解析
-
PHP提示Deprecated: mysql_connect(): The mysql extension is deprecated的解决方法