oracle层次查询

层次查询(Hierarial Retrival)

1.层次查询基本概念
层次查询是描叙一个树状结构，我们可以通过层次查询来控制自上而下，或者自下而上的查询树状数据。并且还可以确定查询起始点
 start with确定开始点
 connect by确定遍历的方向。



2.层次查询语法
select [level],column,expression....
from table/view
[where conditions]
[start with conditions]
[connect by prior conditions]

Eg1:
select level,t.nodename,t.fathernodename from t_topo_bearer t
where t.expiretime = to_date('2099-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
start with t.nodename = 'root'
connect by prior t.nodename = t.fathernodename;

 level是可选的，表示等级，如上图，中心节点的level就是1,江苏省，Node1的级别是2
 from后面可以表格或者视图，但是只能是一个table，如果是视图，那么这个视图不能含join
 where条件是限制条件，但是不会影响层次关系，只是会截断节点，但是截断节点下的子节点不会受影响
 start with 表示开始节点,后面接常规的过滤条件表达式，也可以是子查询
 connect by prior 表示指定父子关系，prior表示上一行记录,后面也接常规的表达式，不可以接子查询，而且限制条件会影响层次结构

3.使用level和lpad函数格式化报表
Level是层次查询中的一个伪劣，使用level必须要有connect by ，可以不要start with
我们经常可以根据level来构造数据

应用
1）.如我们要构造一个时间列表
select 2011 + level -1 from dual connect by level < 10 ;
2)格式化报表
与lpad函数连用格式化报表
lpad是在一个字符串的左边填充字符串，即字符串满足ipad函数中间参数的长度值
eg
select level,lpad(t.nodename,(length(t.nodename)+ level * 2 -2),'-'),t.fathernodename from t_topo_bearer t
where t.expiretime = to_date('2099-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
start with t.nodename = 'root'
connect by prior t.nodename = t.fathernodename;
3)统计树的层次，即树的深度
select count(distinct level) from t_topo_bearer t
where t.expiretime = to_date('2099-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
start with t.nodename = 'root'
connect by prior t.nodename = t.fathernodename;

4)统计每个层次的节点数
select level,count(t.nodename) from t_topo_bearer t
where t.expiretime = to_date('2099-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
start with t.nodename = 'root'
connect by prior t.nodename = t.fathernodename
group by level


4.层次查询中的排序(order)
因为order排序是在层次查询完成后，然后进行排序，所以这样排序是没有太多的意义，因为破坏了层次的结构顺序,为了避免排序时导致层次错乱的问题，我们可以采用Oracle提供的siblings关键字
Eg:
select level,lpad(ename,length(ename)+ level * 2-2,'-'),mgr from emp
start with mgr is null
connect by prior empno = mgr
order siblings by ename;

5.增强特性
1）connect_by_isleaf 是表示一个节点，如果是叶子节点则是1,否则0
Eg
select level,lpad(ename,length(ename)+ level * 2-2,'-'),mgr from emp
where connect_by_isleaf
start with mgr is null
connect by prior empno = mgr;
2）sys_connect_by_path(column,char)，column是字符类型或能自动转换成字符类型的列，它的作用主要是将该列从父节点到当前节点节点通过char连接起来的路径
Eg:
select sys_connect_by_path(ename,'=>')from emp
start with mgr is null
connect by prior empno = mgr;

3) connect_by_root用在列名之前表示此行的根节点的相同列名的值
Eg:
select sys_connect_by_path(ename,'=>'),ename,connect_by_root ename fatherName from emp
start with mgr is null
connect by prior empno = mgr;