1、场景递归到第几层，例如递归到第2层
select level, --层级 wdj.* from wip_discrete_jobs_v wdj where 1 = 1 start with wdj.wip_entity_name = '08363790' --递归开始 connect by wdj.attribute3 = prior wdj.wip_entity_name and level < 3;
2、一行数据出现两行 效果图： select level rn from dual connect by level < 2 ;
3、利用递归快速构造大量测试数据
create table TB_TEST2 ( c_id varchar2(1000), c_date date, c_string varchar2(1000), c_amount number, c_guid varchar2(1000), c_type varchar2(1000) );
批量插入数据：
insert into TB_TEST2
select to_char(rownum) c_id,
sysdate + rownum / 24 / 3600 c_date,
dbms_random.string('x', 20) c_string,
trunc(dbms_random.value(0, 10000000)) c_amount,
sys_guid() c_gruid,
to_char(trunc(dbms_random.value(0, 2))) c_type
from dual
connect by level <= 1000
额外话题字符串排序
select to_number(x.c_id) num_asc, x.c_date, x.c_string, x.c_amount, x.c_guid, x.c_type from tb_test2 x order by to_number(x.c_id) asc
简单树查找 案例: 查找上下级关系
- oracle树查询需要使用到start with ... connect by prior...子句
select empno, ename, mgr, (prior ename) as mgrname --上级名称 from scott.emp start with empno = 7566 --以7566为起点向下递归查找 connect by (prior empno) = mgr --上一级的员工编码等于本级的上级领导编码
根节点、叶子节点、分支节点 案例：判别是否为根节点、叶子节点、分支节点 根节点：整个树的最上层(即level = 1) 叶子节点：在树中没有子级(即connect_by_isleaf = 1) 分支节点：排除根节点以及不是叶子节点的都是分支节点(即connect_by_isleaf = 0 and level > 1)
select e.empno, e.ename, e.mgr, (prior ename) as mgrname, level as 层级, case when level = 1 then '根节点' else null end as 是否根节点, case when connect_by_isleaf = 1 then '叶子节点(该员工不属于其他员工的上级)' else null end as 是否叶子节点, case when connect_by_isleaf = 0 and level > 1 then --需要排除根节点(level = 1) '分支节点' else null end as 是否分支节点 from scott.emp e start with e.empno = 7566 connect by (prior empno) = e.mgr
查看根节点到当前节点的路径
select t.empno, t.ename, t.mgr, t.mgrname, substr(t.enames, 2) as enames from (select e.empno, e.ename, e.mgr, (prior ename) as mgrname, sys_connect_by_path(e.ename, ',') as enames from scott.emp e start with e.empno = 7566 connect by (prior empno) = e.mgr) t
树查询中的排序不能直接使用order by,而应该使用order siblings by, 对于树形数据,我们只需要针对同一条路径下的分支进行排序,这样就不会影响到整棵树的数据 --直接加order by
select lpad('-', (level - 1) * 2, '-') || e.empno as empno,
e.empno,
e.ename,
e.mgr,
(prior ename) as mgrname
from scott.emp e
start with e.empno = 7566
connect by (prior empno) = e.mgr
order by e.empno desc;
--使用order siblings by
select lpad('-', (level - 1) * 2, '-') || e.empno as empno,
e.empno,
e.ename,
e.mgr,
(prior ename) as mgrname
from scott.emp e
start with e.empno = 7566
connect by (prior empno) = e.mgr
order siblings by e.empno desc
树查找中的where子句
- 在树形查询中,where子句过滤的对象是树查询的结果,所以如果要先过滤再进行树查询,那么需要嵌套一个子查询
- select e.empno, e.ename, e.mgr from scott.emp e where e.deptno = 20;
- 如上图，20号部门没有mgr is null的数据。
- --先查询树数据,再进行过滤
select e.empno, e.ename, e.mgr, e.deptno, (prior ename) as mgrname from scott.emp e where e.deptno = 20 start with e.mgr is null connect by (prior empno) = e.mgr;
--等价于
select * from (select e.empno, e.ename, e.mgr, e.deptno, (prior ename) as mgrname from scott.emp e start with e.mgr is null connect by (prior empno) = e.mgr) t where t.deptno = 20;
如果使用以上sql直接加where的话，数据过滤就不正确了，下面那个sql就是等价的，它是先进行树查询，然后再过滤整个树查询结果，因为20号部门没有mgr is null的数据，而看下面的查询结果却返回了数据，所以数据查询不正确
正确的做法如下：
--先过滤数据,再进行树查询
select e.empno, e.ename, e.mgr, e.deptno, (prior ename) as mgrname from (select * from scott.emp where deptno = 20) e where e.deptno = 20 start with e.mgr is null connect by (prior empno) = e.mgr;
查询树形的一个分支
- 查询树形的分支不能使用where,只需要通过start with xx指定开始遍历的节点即可
select e.empno, e.ename, e.mgr, (prior ename) as 所属上级, level as 层次, decode(connect_by_isleaf, 1, '是', '否') as 是否叶子节点 from scott.emp e start with e.empno = 7698 connect by (prior empno) = e.mgr
去除树形的一个分支
- 同查询树形的分支不能使用where,如果要去除树形的一个分支, 因为树查询的过滤条件通过connect by (xx = yy)指定,所以可以直接在connect by 后面加条件过滤即可
select e.empno, e.ename, e.mgr, (prior ename) as 所属上级, level as 层次, decode(connect_by_isleaf, 1, '是', '否') as 是否叶子节点 from scott.emp e start with e.mgr is null connect by (prior empno) = e.mgr and e.empno != 7698 --去除某个分支
标签：ename,遍历,递归,prior,empno,mgr,connect,Oracle,节点 From： https://www.cnblogs.com/ivenlin/p/18121125