作者介绍
丁俊，DBAplus社群联合发起人，性能优化专家，Oracle ACEA。电子工业出版社终身荣誉作者，《剑破冰山-Oracle开发艺术》副主编。8年电信行业从业经验，从事系统开发与维护、业务架构和数据分析、系统优化等工作。擅长基于Oracle的系统优化，精通SQL、PL SQL、JAVA等。
一、基本概念
对于层次查询需要掌握：
1）理解层次查询的基本概念，识别需求中何时要用到层次查询的能力。
2）建立和格式化树形报表(tree report)。
3）修剪树形结构的节点(node)和枝(branches)。
4）掌握层次查询中的各种关键字和10G新特性。
关键词：tree，root(根)，node，leaf(叶子)，branch(树枝，分支)
子句：START WITH、CONNECT BY、10g的ORDER SIBLINGS BY
操作符：PRIOR、10g的CONNECT_BY_ROOT
伪列：LEVEL,10g的CONNECT_BY_ISCYCLE、CONNECT_BY_ISLEAF
参数或关键字：10g的NOCYCLE
函数：SYS_CONNECT_BY_PATH
解决问题：数据构造、连续数问题、线路规划问题、格式化树形结构、字符串合并等。
本节例子来源于表s_emp,表结构和数据如下：
看上面的表s_emp，使用层次查询，我们可以获得一张表基于层次关系的数据集合。Oracle是一种关系型数据库，在表中不可能以层次的关系存放数据。但是我们可以通过一定的规则，使用tree walking(树的遍历或树的查找)来获得层次关系的数据。Hierarical query是一种获得树的层析关系报表的方法。
树形结构的数据集合，存在于我们日常生活中的很多地方，比如考虑一个家族关系，有长辈，长辈下面有子女，子女下面还可以有子女，这转化为层次或等级关系就是：根节点只有一个，下面有子节点，子节点下面还有子节点，这样组成了一棵树。(有时候，根节点root不一定只有一个，严格意义上说，这种情况不是一个严格的树)
当一种层次关系是存在一个表的行中，那么这种层次是可以获得的。例如，我们看s_emp表，对于title:VP，我们知道这些行中都包含manager_id=1,也就是说，这些行属于id=1的雇员的下属雇员，那么有title=vp又可以获得一系列的层次，而这些层次的跟则是id=1这个雇员。由此，得到一棵树形结构数据集合。
层次树或等级树，在譬如家族关系，育种关系，组织管理，产品装配，人类进化，科学研究等领广泛应用。
下面我们就根据s_emp这张表，根据职位大小来描述一个树形结构图。如图：
（只显示部分树形结构）
树形结构的父子关系，你可以控制：
1）遍历树的方向，是自上而下，还是自下而上。
2）确定层次的开始点(root)的位置。
层次查询语句正是从这两个方面来确定的，start with确定开始点，connect by确定遍历的方向。
二、层次查询介绍
>>>>
1、语法：
层次查询是通过start with和connect by子句标识的。
1）其中level关键字是可选的，表示等级，表示root,2表示root的child,其他相同的规则。它可以在where，connect by，select里存在。
2）where条件限制了查询返回的行，但是不影响层次关系，不满足条件的节点不返回，但是这个不满足条件的节点的下层child不受影响。因为where是在start with和connect by之后执行的（join是在connect by之前做的）。
3）start with是表示开始节点（root节点），对于一个真实的层次关系，必须要有这个子句，但是不是必须的，没有start with表示每行都是根然后遍历。
4）connect by prior是指定父子关系，选择子节点，其中prior的位置不一定要在connect by之后，对于一个真实的层次关系，这也是必须的，不能省略。
5）一般层次查询不能直接order by，会破坏层次，但是10g可以使用ORDER SIBLINGS BY。
>>>>
2、遍历树
Start with子句：
首先必须确定startpoint，通过start with子句，后面加条件，这个条件是任何合法的条件表达式。
Start with确定将哪行作为root，如果没有start with,则每行都当作root，然后查找其后代，这不是一个真实的查询。start with后面可以使用子查询，如果有where条件，则会截断层次中的相关满足条件的节点，但是不影响整个层次结构。可以带多个条件。
对于s_emp,从root title=president开始，语句如下：
select level,id,manager_id,last_name,title from s_emp
start with manager_id is null
connect by prior id=manager_id;
Connect by子句：
Connect by与prior确定一个层次查询的条件和遍历的方向(prior确定)。
Connect by prior column_1=column_2;(其中prior表示前一个节点的意思，可以在connect by等号的前后，列之前，也可以放到select中的列之前)。
Connect by也可以带多个条件，比如 connect by prior id=manager_id and id>10;
connect by也可以带level之类的，再比如connect_by_isleaf,connect_by_root,有的在低版本比如10.1上可能报错，高版本没有问题，如connect_by_root。
遍历方向：
1）自顶向下遍历：就是先由根节点，然后遍历子节点。column_1表示父key,column_2表示子key。即这种情况下：connect by prior 父key=子key表示自顶向下，等同于connect by 子key=prior 父key.
例如：
select level,id,manager_id,last_name, title from s_emp
start with manager_id=2
connect by id=prior manager_id;--自下而上遍历
2）自底向上遍历：就是先由最底层的子节点，遍历一直找到根节点。与上面的相反。
Connect by之后不能有子查询，但是可以加其他条件,比如加上and id !=2等。这句话则会截断树枝，如果id=2的这个节点下面有很多子孙后代，则全部截断不显示。比如下面的句子：
select level,id,manager_id,last_name,title from s_emp
start with title=(select title from s_emp where manager_id is null)
connect by prior id=manager_id and id!=2;
不来不加上id!=2，共有25条记录，现在加上这个条件只有9条记录了，因为id=2的后代包括自己共有16条记录，全部被截断。
注意connect by中用level修剪的问题：在connect by里修剪效率比where过滤好，因为不用全部递归，注意level条件。
SELECT ID,manager_id,LEVEL
FROM s_emp
START WITH manager_id IS NULL
CONNECT BY PRIOR ID = manager_id
AND LEVEL <=3
;
第1次递归level=2，满足条件，则继续下次递归到3结束，如果是level>1也可以，level>=2也可以，相当于没有加level，但是level>3则第1次递归不满足，也就是返回根了。level=3同样，要从原理上搞清楚递归。
>>>>
3、使用level和lpad格式化报表
Level是层次查询的一个伪列，如果有level，必须有connect by，start with可以没有。Lpad是在一个string的左边添加一定长度的字符，并且满足中间的参数长度要求，不满足自动添加。例如现在的需求是，输出s_emp等级报表，root节点的last_name不变，比如第2等级，也就是level=2的前面加两个’_’符号，level=3的前面加4个。这样我们可以得到一个公式就是：
Lpad(last_name,length(last_name)+(level*2)-2,’_’),lpad对中文处理有点问题，用lenthb
可以得出下面的语句：
select level,id,manager_id,lpad(last_name,length(last_name)+(level*2)-2,'_'),title,prior last_name from s_emp
start with manager_id is null
connect by prior id=manager_id;
select中的prior last_name是输出其父亲的last_name.这个语句执行的结果如下：
再如：
select level,id,manager_id,lpad('|--'||last_name,length('|--' ||last_name)+(level*5-5),' '),title,prior last_name from s_emp
start with manager_id is null
connect by prior id=manager_id;
--因为第2个没有用level和括号拼凑，所以改为-2，显示层次数
select level,id,manager_id,lpad('|('||level||')--'||last_name,length('|--' ||last_name)+(level*5-2),' '),title,prior last_name from s_emp
start with manager_id is null
connect by prior id=manager_id;
select level,id,manager_id,lpad('--'||level||'--'||last_name,length(last_name)+(level*5-2),' '),title,prior last_name from s_emp
start with manager_id is null
connect by prior id=manager_id;
>>>>
4、修剪branches
上面已经提到，where子句会将节点删除，但是其后代不会受到影响，connect by 中加上条件会将满足条件的整个树枝包括后代都删除。要注意，如果是connect by之后加条件正好条件选到根，那么结果和没有加一样，对于结果的过滤，如果能加在connect by之后，则尽量加在connect by之后，从而尽早过滤数据，提高效率<connect by是发生在where之前的>。
如图所示：
>>>>
5、层次查询限制
1）层次查询from之后如果是table，只能是一个table，不能有join。
2）from之后如果是view，则view不能是带join的。
3）使用order by子句，order子句是在等级层次做完之后开始的，所以对于层次查询来说没有什么意义，除非特别关注level，获得某行在层次中的深度，但是这两种都会破坏层次。见5.3增强特性中的使用siblings排序。
4）在start with中表达式可以有子查询，但是connect by中不能有子查询。
以上是10g之前的限制，10g之后可以使用带join的表和视图，connect by中可以使用子查询。
理解关键操作：
层次查询的关键操作组件就是start with，connect by start with确定树的开始节点，如果这个语句确定的开始节点有多个（没有start with语句，那么每行都是一个根节点），那么根据层次查询的算法规则，会有多个符合start with条件的节点作为不同树的根节点，然后以每个根节点为开始搜索点，根据connect by条件来搜索符合条件的子节点，当所有符合connect by条件的根节点都找完其子节点，层次查询结束。 层次查询是在同一层where条件之前执行的，这个要注意，所以where条件不会破坏层次查询的节点所属根和层次(level),where只是简单的起到过滤结果的作用。这个容易出错，不要以为where写在from后connect by前就认为where先执行，那是不正确的，看下面结果(scott下的表)：--最普通的层次查询，根节点mgr is null确定只有一个，empno=7839为root
SQL> select empno,mgr,sys_connect_by_path(mgr,'->') ptree from emp 2 start with mgr is null 3 connect by prior empno = mgr;EMPNO MGR PTREE----- ----- ---------------------------------------------------------------------------7839 ->7566 7839 ->->78397902 7566 ->->7839->75667369 7902 ->->7839->7566->79027698 7839 ->->78397499 7698 ->->7839->76987521 7698 ->->7839->76987654 7698 ->->7839->76987844 7698 ->->7839->76987900 7698 ->->7839->76987782 7839 ->->78397934 7782 ->->7839->778212 rows selected
--看start with增加了个or条件，那么现在的根就有两个，一个是上面的，另一个是mgr=7566,因此，下面的结果包含了：
--上面的结果，另外多了棵树的根是7566的，第1和第2行--你的sql如果把start with中的flag=0却掉就是这种情况了，暂且不讨论你的connect by中flag=0去掉的情况
SQL> select empno,mgr,sys_connect_by_path(mgr,'->') ptree from emp 2 start with mgr is null or mgr =7566 3 connect by prior empno = mgr;EMPNO MGR PTREE----- ----- ------------------------------------------------------------------------7902 7566 ->75667369 7902 ->7566->79027839 ->7566 7839 ->->78397902 7566 ->->7839->75667369 7902 ->->7839->7566->79027698 7839 ->->78397499 7698 ->->7839->76987521 7698 ->->7839->76987654 7698 ->->7839->76987844 7698 ->->7839->76987900 7698 ->->7839->76987782 7839 ->->78397934 7782 ->->7839->778214 rows selected
--看增加where条件，只会的两行结果，但是mrg=7566所属的层次和所属的树都是没有变的，这相当于从两棵树上剪下两棵树枝，又叫树的修剪。
--可以看出where是在connect by之后起作用的，如果在之前起作用，就一个节点，何来树呢？看计划也可以的。
SQL> select empno,mgr,sys_connect_by_path(mgr,'->') ptree from emp 2 where mgr=7566 3 start with mgr is null or mgr =7566 4 connect by prior empno = mgr 5 ;EMPNO MGR PTREE----- ----- --------------------------------------------------------------------7902 7566 ->75667902 7566 ->->7839->7566
>>>>
6、应用
1）查询每个等级上节点的数目
先查看总共有几个等级：
select count(distinct level) -- max(level)也可以
from s_emp
start with manager_id is null
connect by prior id=manager_id;
要查看每个等级上有多少个节点，只要按等级分组，并统计节点的数目即可，可以这样写：
select level,count(last_name)
from s_emp
start with manager_id is null
connect by prior id=manager_id
group by level;
2）格式化报表
详见：第三小节.使用level和lpad格式化报表
3）查看等级关系
有一个常见的需求，比如给定一个具体的emp看是否对某个emp有管理权，也就是从给定的节点寻找，看其子树节点中能否找到这个节点。如果找到，返回，找不到，no rows returned.
比如对于s_emp表，从根节点，也就是manager_id is null的开始找，看first_name=’ Elena’是否被它管理，语句如下：
select level,a.* from
s_emp a
where first_name='Elena' –被管理的节点
start with manager_id is null –开始节点
connect by prior id=manager_id;
4）删除子树
比如有这样的需求，现在要裁员，将某个部门的员工包括经理全部裁掉，那么可以使用树形查询作为子查询实现这个功能。
将id为2的员工管理的所有员工包括自己删除。因为要全部裁掉了。那么语句如下：
delete from s_emp where id in(
select id from
s_emp a
start with id=2 –从id=2的员工开始查找其子节点，把整棵树删除
connect by prior id=manager_id);
5）找出每个部门的经理
这个需求，我们可以从树中查找，也就是对于每个部门选最高等级节点。可以使用connect by后加条件过滤branches的方法。
select level,a.* from
s_emp a
start with manager_id is null
connect by prior id=manager_id and dept_id !=prior dept_id;--当前行的dept_id不等于父亲的dept_id，即每个子树中选最高等级节点
6）查询一个组织中最高的几个等级
用where level条件过滤
select level,a.* from
s_emp a
where level <=2 –查找前两个等级
start with manager_id is null
connect by prior id=manager_id and dept_id !=prior dept_id;
改进：将level<=2加在connect by之后，尽早删除不必要的分支，提高效率。
select level,a.* from
s_emp a
start with manager_id is null
connect by prior id=manager_id and dept_id !=prior dept_id and level<=2;
7）合计层次
有两个需求，一是对一个指定的子树subtree做累加计算salary，一是将每行都作为root节点，然后对属于这个节点的所有叶子点累加计算salary（如果不包含根节点，可以使用10g的CONNECT_BY_ISLEAF=1只求子节点）。
第一种很简单，求下sum就可以了，语句：
select sum(salary) from
s_emp a
start with id=2—比如从id=2开始
connect by prior id=manager_id;
第2个需求，需要用到第1个，对每个root节点求这个树的累加值，然后内部层次查询的开始节点从外层查询获得。
select last_name,salary,(
select sum(salary) from
s_emp
start with id=a.id --让每个节点都成为root
connect by prior id=manager_id) sumsalary
from s_emp a;
SELECT id,SUM(salary)
FROM(
select salary,connect_by_root ID ID from
s_emp
connect by prior id=manager_id
)
GROUP BY ID;
实例：有数据表结构如下， 只有叶子节点有数据。
id parentId name amount1 成本 2 1 工资 3 2 基本工资 1000 4 2 奖金 2005 1 保险 400
现在想统计处父节点合计数，如下：
1 成本 1600 //2 + 5 2 工资 1200 //3 + 43 基本工资 1000 4 奖金 2005 保险 400
with tmp as ( select 1 as id , null as parentid , '成本' as name , null as amount from dual union all select 2,1 , '工资', null from dual union all select 3,2 , '基本工资', 1000 from dual union all select 4,2 , '奖金' , 200 from dual union all select 5,1 , '保险' , 400 from dual)SELECT root_id,SUM(amount) FROM (select CONNECT_BY_ROOT(id) root_id,amount from tmp WHERE CONNECT_BY_ISLEAF=1 CONNECT BY PRIOR id = parentid )GROUP BY root_id;
--因为根节点没有amount,所以不需要去掉，如果有amount,而不需要包含根节点，则用10G的CONNECT_BY_ISLEAF=1只查子节点。
with tmp as (
select 1 as id , null as parentid , '成本' as name , null as amount from dual union all
select 2,1 , '工资', null from dual union all
select 3,2 , '基本工资', 1000 from dual union all
select 4,2 , '奖金' , 200 from dual union all
select 5,1 , '保险' , 400 from dual
)
SELECT id,parentid,name,
( SELECT SUM(amount) FROM
tmp a
-- WHERE CONNECT_BY_ISLEAF=1
START WITH a.id=b.id
CONNECT BY PRIOR a.id=a.parentid
) sum_sal
FROM
tmp b
ORDER BY 1;
---9i可以使用SYS_CONNECT_BY_PATH然后取第1个节点---
将CONNECT_BY_ROOT用下面的代替：
substr(sys_connect_by_path(id,'/'),2,
decode(instr(sys_connect_by_path(id,'/'),'/',1,2),
0,length(sys_connect_by_path(id,'/'))-1,
instr(sys_connect_by_path(id,'/'),'/',1,2)- instr(sys_connect_by_path(id,'/'),'/',1,1)-1 )
) root_id
8）找出指定层次中的叶子节点
Leaf(叶子)就是没有子孙的孤立节点。Oracle 10g提供了一个简单的connect_by_isleaf=1,0表示非叶子节点。
select level,id,manager_id,last_name, title from s_emp
where connect_by_isleaf=1 –表示查询叶子节点
start with manager_id=2
connect by prior id=manager_id;
下面的方法也可以，NOT EXISTS表示找到的没有子孙的节点，这种方法在10g之前比较好。
SELECT LEVEL,id,manager_id,last_name, title
FROM s_emp a
WHERE NOT EXISTS (SELECT 1 FROM s_emp b WHERE b.manager_id = a.id)
START WITH manager_id =2 CONNECT BY PRIOR id=manager_id;
注意：level不可以前面加表名。
其他：
Connect by与rownum的联合使用，比如给定两个日期，查询中间所有的日期，按月递增。
SELECT to_date('2008-10-1', 'YYYY-MM-DD') + ROWNUM - 1
FROM dual
CONNECT BY rownum <= to_date('2008-10-5', 'YYYY-MM-DD') -
to_date('2008-10-1', 'YYYY-MM-DD') + 1;
获取01到99
select case when length(rownum)=1 then to_char('0')||rownum else to_char(rownum) end
from dual
connect by rownum<=99;
select lpad(rownum,2,'0') from dual connect by rownum<=99;
9）在合并行上使用
比如：
create table t_test (l_pro_id varchar2(16), r_pro_id varchar2(16));INSERT INTO t_test VALUES(1,10);INSERT INTO t_test VALUES(1,11);INSERT INTO t_test VALUES(1,12);INSERT INTO t_test VALUES(1,13);INSERT INTO t_test VALUES(1,14);INSERT INTO t_test VALUES(1,15);INSERT INTO t_test VALUES(1,16);INSERT INTO t_test VALUES(1,17);
INSERT INTO t_test VALUES(2,2);INSERT INTO t_test VALUES(2,3);INSERT INTO t_test VALUES(2,4);INSERT INTO t_test VALUES(2,5);INSERT INTO t_test VALUES(2,6);INSERT INTO t_test VALUES(2,7);INSERT INTO t_test VALUES(2,8);INSERT INTO t_test VALUES(2,9);
COMMIT;
要求结果
l_pro_id r_pro_list -------------------------------- 1 10,11,12,13,14,15,16,17 2 2,3,4,5,6,7,8,9
可以使用wmsys.wm_concat，也可以使用层次查询，这里用层次查询。
select l_pro_id,ltrim(max(sys_connect_by_path(r_pro_id,',')),',') --需要剔除第1个逗号 from (select l_pro_id,r_pro_id,row_number() over(partition by l_pro_id order by r_pro_id) rn from t_test) start with rn =1 connect by prior rn = rn-1 and prior l_pro_id=l_pro_id group by l_pro_id order by l_pro_id;
10）找任意两节点之间的最大路径
drop table t;
create table t(a number,b number);
select * from t for update;
insert into t values(1,2);
insert into t values(2,3);
insert into t values(3,4);
insert into t values(4,5);
insert into t values(1,5);
insert into t values(5,9);
insert into t values(1,8);
insert into t values(7,3);
insert into t values(8,9);
commit;
--用sys_connect_by_path,下面的是从根找到叶子
select '1'||sys_connect_by_path(b,'->') path from t
where connect_by_isleaf=1
start with a=1
connect by prior b = a;
1->2->3->4->5->9
1->5->9
1->8->9
--这个找任意开始到结束节点，注意如果有循环的要加nocycle,10g才有
select *
from (select ltrim(sys_connect_by_path(a, '->') || '->' || b, '->') r
from t x
start with x.a = 1
connect by prior x.b = x.a)
where instr(r, '9') > 0
三、增强特性
>>>>
1、SYS_CONNECT_BY_PATH
Oracle 9i提供了sys_connect_by_path(column,char)，其中column是字符型或能自动转换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。这个函数只能使用在层次查询中。
例如，要求将s_emp表中的层次关系展现出来，并且将last_name按照’=>’展现。如root,则是=>root_last_name, level=2的就是=>root_last_name=>level_2_last_name,并且利用lpad格式化报表。语句是：
select last_name,
level,
id,
lpad(' ', level * 2 - 1) || sys_connect_by_path(last_name, '=>') –前面按层次加空格，--并且后面加上路径
from s_emp
start with manager_id is null
connect by prior id = manager_id;
结果如图所示：
下面的是oracle10g新增特性。
>>>>
2、CONNECT_BY_ISLEAF伪列
在oracle9i的时候，查找指定root下的叶子节点，是很复杂的，oracle10g引入了一个新的伪列，connect_by_isleaf,如果行的值为0表示不是叶子节点，1表示是叶子节点。
找出s_emp中找出manager_id=2开始的行为root，表示叶子节点和非叶子节点，那么语句如下：
----找根节点为a,对应的所有叶子节点。
>>>>
3、CONNECT_BY_ISCYCLE伪列和NOCYCLE关键字
如果从root节点开始找其子孙，找到一行，结果发生和祖先互为子孙的情况，则发生循环，Oracle会报ORA-01436: CONNECT BY loop in user data，在9i中只能将发生死循环的不加入到树中或删除，在10g中可以用nocycle关键字加在connect by之后，避免循环的参加查询操作。并且通过connect_by_iscycle得到哪个节点发生循环(也就是onnect_by_iscycle伪列只能与nocycle连用)。0表示未发生循环，1表示发生了循环，如：
结果是：
>>>>
4、CONNECT_BY_ROOT
Oracle10g新增connect_by_root，用在列名之前表示此行的根节点的相同列名的值（这个和PRIOR一样，是层次查询的操作符）。如：
select connect_by_root last_name root_last_name, connect_by_root id root_id,
id,last_name,manager_id
from s_emp
start with manager_id is null
connect by prior id=manager_id;
结果为：
9i办法：用SYS_CONNECT_BY_PATH, 然后用INSTR, SUBSTR把第一截解析出来。
上面的可以改写为：
//选第2次出现分割符的位置，然后截取，因为根节点只有一个分隔符，所以加个decode.
>>>>
5、使用SIBLINGS关键字排序
前面说了，对于层次查询如果用order by排序，比如order by last_name则是先做完层次获得level，然后按last_name排序，这样破坏了层次，比如特别关注某行的深度，按level排序，也是会破坏层次的。
在oracle10g中，增加了siblings关键字的排序。
语法：order siblings by <expre>
它会保护层次，并且在每个等级中按expre排序。
select level,
id,last_name,manager_id
from s_emp
start with manager_id is null
connect by prior id=manager_id
order siblings by last_name;
结果如图：
9i 办法：
本文来自云栖社区合作伙伴"DBAplus"，原文发布时间：2016-09-18
