分层查询





修剪分支

1.  简单的树形查询
     我们经常会用一些表来保存上下级的关系，如地区表、员工表、组织机构表等，为了按照上下级关系递归的调用这些数据，就需要用到树形结构，以emp表为例。
语句：

select empno as员工编号, ename as姓名, (prior ename) as主编姓名
  from emp startwith empno = '7566'connectby (prior empno) = mgr;
分析：

（1）起点：这个语句以empno = '7566'为起点向下递归查询。
（2）通过操作符prior可以取得上一级的信息，如上面的查询中的主管姓名（prior ename）。
（3） connect by 字句列出了（上一级的编码）等于本级的主管编码。
结果：

 2. 根节点、分支节点、叶子节点
      在树形结构中常用的两个伪列：level和connect_by_isleaf。level返回当前行所在的等级，connect_by_isleaf返回当前行所在的等级，根节点为1级，其下为2级…….
如果当前节点下没有其他的节点，则connect_by_isleaf返回1，否则返回0。这样就是可以通过level和connect_by_isleaf来判断标识根节点、分支节点与叶子节点。
语句：

select lpad('-', (level - 1) * 2, '-') || empo as员工编号,
       mgr as主管编号,
       levelas级别,
       decode(level, 1, 1) as根节点decode(connect_by_isleaf, 1, 1) as叶子节点,
       case
         when (connect_by_isleaf = 0andlevel > 1) then
          1
       endas分支节点
  from emp
 startwith empo = 7566
connectby (prior empo) = mgr;
结果：

                
 ORACLE是一个关系数据库管理系统,它用表的形式组织数据,在某些表中的数据还呈现出树型 结构的联系。
 例如有如下案例：


数据为节选，字段值含义分别为税务机构代码、税务机构名称、上级税务机构代码，税务机构级别
select * from extern_dm_swjg查询的时候默认顺序就是上面的顺序，可以看出是混乱的并没有特殊结构特征。

而希望的结果如下图：

sj_swjg_dm为空即根节点的排在第一个，仔细观察 上图是树结构查询结果，可能不太直观，看下图就清楚了


1． 树结构的描述 
树结构的数据存放在表中，数据之间的层次关系即父子关系，通过表中的列与列间的关系来描述，
通过每个节点的父节点，就可以确定整个树结构。
在SELECT命令中使用CONNECT BY和START WITH 子句可以查询表中的树型结构关系。其命令格式如下：
SELECT ... 
CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 列名2} 
[START WITH]... 
其中：CONNECT BY子句说明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的关系中。PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，PRIOR运算符在的一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。
START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。

例1 以树结构方式显示表的数据。
select swjg_dm,swjg_mc,sj_swjg_dm,swjg_levelfrom extern_dm_swjgconnect by prior swjg_dm = sj_swjg_dmstart with sj_swjg_dm is null

2． 关于PRIOR 
运算符PRIOR被放置于等号前后的位置，决定着查询时的检索顺序。

例2从节点开始自底向上查询
select swjg_dm,swjg_mc,sj_swjg_dm,swjg_levelfrom extern_dm_swjgconnect by  swjg_dm =  prior sj_swjg_dmstart with swjg_dm = '16107100004'

3．使用LEVEL 
    在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2， 依此类推。 
    在查询中，可以使用伪列LEVEL显示每行数据的有关层次。LEVEL将返回树型结构中当前节点的层次，我们可以使用LEVEL来控制对树型结构进行遍历的深度。

例3 显示表中的各行数据及层号。
select level, swjg_dm,swjg_mc,sj_swjg_dm,swjg_levelfrom extern_dm_swjgconnect by prior swjg_dm = sj_swjg_dmstart with sj_swjg_dm is null

伪列LEVEL为数值型，可以在SELECT 命令中用于各种计算。 

例4 使用LEVEL改变查询结果的显示形式。
select LPAD(LEVEL,LEVEL*3,' ') as "LEVEL", swjg_dm,swjg_mc,sj_swjg_dm,swjg_levelfrom extern_dm_swjgconnect by prior swjg_dm = sj_swjg_dmstart with sj_swjg_dm is null 

在SELECT使用了函数LPAD,关于LPAD函数的使用，请参阅Oracal的Lpad函数
4．节点和分支的裁剪 
    在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。

例5  仅剪去了树中单个节点16107100003 扶风县国家税务局
select LPAD(LEVEL,LEVEL*3,' ') as "LEVEL", swjg_dm,swjg_mc,sj_swjg_dm,swjg_levelfrom extern_dm_swjgwhere swjg_dm !='16107100003'connect by prior swjg_dm = sj_swjg_dmstart with sj_swjg_dm is null

 在这个查询中，仅剪去了树中单个节点swjg_dm为16107100003的，可它的子节点依然存在。若希望剪去树结构中的某个分支，
 则要用CONNECT BY 子句。CONNECT BY 子句是限定树型结构中的整个分支，既要剪除分支上的单个节点，
 也要剪除其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。

例6  除去节点16107100003的一支
select LPAD(LEVEL,LEVEL*3,' ') as "LEVEL", swjg_dm,swjg_mc,sj_swjg_dm,swjg_levelfrom extern_dm_swjgconnect by prior swjg_dm = sj_swjg_dmand swjg_dm !='16107100003'start with sj_swjg_dm is null

    这个查询结果就与例5不同，除了剪去单个节点 16107100003 外，还将 16107100003 的子节点16107100004剪掉，即把 16107100003  这个分支剪掉了。 
    当然WHERE子句可以和CONNECT BY子句联合使用，这样能够同时剪掉单个节点和树中的某个分支。

在使用SELECT 语句来报告树结构报表时应当注意，CONNECT BY子句不能作用于出现在WHERE子句中的表连接。如果需要进行连接，可以先用树结构建立一个视图，再将这个视图与其他表连接，以完成所需要的查询。


            
再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！https://blog.csdn.net/jiangjunshow

                                        
                                            

	ORACLE分层查询start with&connect by


	


	分层查询是select 语句的扩展，目的是迅速找出表中列-列的隶属关系。


	


	19.1 树的遍历


	


	ORACLE是一个关系数据库管理系统,它用表的形式组织数据,在某些表中的数据还呈现出树型结构的联系。例如，我们现在讨论雇员信息表EMP，其中含有雇员编号（EMPNO）和经理（MGR）两列，通过这两列反映出来的就是雇员之间领导和被领导的关系。他们之间的这种关系就是一种树结构。


	


	

	


	树的遍历有两个方向


	


	top--down 自上而下


	即父亲找儿子，一个父亲可能有几个儿子，一个儿子可能有几个孙子，遍历不能丢了儿子，顺序以左为先。


	


	down--top 自底向上


	即儿子找父亲，一个儿子只能有一个父亲，所以顺序应该是：孙子->儿子-->父亲-->爷爷。


	


	19.2 CONNECT BY 和 START WITH


	


	在SELECT命令中使用CONNECT BY 和 START WITH 子句可以查询表中的树型结构关系。其命令格式如下：


	


	SELECT ...


	CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 列名2}


	[START WITH]；


	


	19.3 关于CONNECT BY子句


	


	理解CONNECT BY PRIOR 子句至关重要，它确定了树的检索方向: 是top --> down（父-->子）还是down --> top（子-->父）。


	在分层表中，表的父列与子列是确定的（身份固定），如：在emp表中empno是子列（下级）， mgr是父列（上级）。


	RIOR关键字就像一个箭头("-->")，


	connect by prior empno = mgr


	connect by mgr = prior empno


	两句语法等同，都是说mgr（父）--> empno(子），因此树的检索方向是top --> down。


	connect by empno = prior mgr


	connect by prior mgr = empno


	两句语法等同，都是说empno(子）--> mgr(父），因此树的检索方向是down --> top。


	


	19.4 START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点（每一行都会成为一个树根）。


	


	例1 以树结构方式显示EMP表的数据。


	

	SQL>select empno,ename,mgr from emp connect by prior empno=mgr start with empno=7839;


	

	仔细看empno这一列输出的顺序，就是上图树状结构每一条分支（从根节点开始）的结构。


	mgr（父）--> empno(子），因此树的检索方向是top --> down。  


	


	例2 从SMITH节点开始自底向上查找EMP的树结构。


	

	SQL>select empno,ename,mgr


	from emp


	connect by empno=prior mgr


	start with empno=7369 


	/


	

	在这种自底向上的查找过程中，只有树中的一枝被显示。


	empno(子）--> mgr(父），因此树的检索方向是down --> top


	


	19.5  定义查找起始节点


	在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。


	

	例3 查找7566(JONES)直接或间接领导的所有雇员信息。


	


	SQL＞SELECT EMPNO,ENAME,MGR


	FROM EMP


	CONNECT BY PRIOR EMPNO=MGR


	START WITH EMPNO=7566


	/


	

	


	


	


	


	


	


	


	


	


	START WITH 不但可以指定一个根节点，还可以指定多个根节点。


	例4 查找由FORD和BLAKE 领导的所有雇员的信息。


	

	SQL>SELECT EMPNO,ENAME,MGR


	FROM EMP


	CONNECT BY PRIOR EMPNO=MGR


	START WITH ENAME IN ('FORD','BLAKE')


	/      父亲找儿子


	

	


	19.6 使用LEVEL


	在查询中，可以使用伪列LEVEL显示每行数据的有关层次。LEVEL将返回树型结构中当前节点的层次。


	伪列LEVEL为数值型，可以在SELECT 命令中用于各种计算。


	例5 使用LEVEL改变查询结果的显示形式。


	SQL> COLUMN LEVEL FORMAT A20


	SQL> SELECT LPAD(LEVEL,LEVEL*3,' ')


	as "LEVEL",EMPNO,ENAME,MGR


	FROM EMP


	CONNECT BY PRIOR EMPNO=MGR


	START WITH ENAME='KING'


	/


	在SELECT使用了函数LPAD，该函数表示以LEVEL*3个空格进行填充，由于不同行处于不同的节点位置，具有不同的LEVEL值，因此填充的空格数将根据各自的层号确定，空格再与层号拼接，结果显示出这种层次关系。


	

	

	


	


	只查看第2层的员工信息：


	SQL> select t1.* from (select level LNUM ,ename,mgr from emp connect by prior empno=mgr start with ename='KING') t1 where LNUM=2;


	

	


	19.7 节点和分支的裁剪


	


	在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。


	


	SQL>SELECT LPAD(LEVEL,LEVEL*3,' ')


	as "LEVEL",EMPNO,ENAME,MGR


	FROM EMP


	WHERE ENAME<>'SCOTT'


	CONNECT BY PRIOR EMPNO=MGR


	START WITH ENAME='KING'


	/


	

	


	


	


	在这个查询中，仅剪去了树中单个节点SCOTT。若希望剪去树结构中的某个分支，则要用CONNECT BY 子句。CONNECT BY 子句是限定树型结构中的整个分支，既要剪除分支上的单个节点，也要剪除其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。


	


	例8．显示KING领导下的全体雇员信息，除去SCOTT领导的一支。


	


	SQL>SELECT LPAD(LEVEL,LEVEL*3,' ') as "LEVEL",EMPNO,ENAME,MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR AND ENAME!='SCOTT' START WITH ENAME='KING';


	/


	

	

	                           













这个查询结果就除了剪去单个节点SCOTT外，还将SCOTT的子节点ADAMS剪掉，即把SCOTT这个分支剪掉了。 


	


	当然WHERE子句可以和CONNECT BY子句联合使用，这样能够同时剪掉单个节点和树中的某个分支。


	


	例9．显示KING领导全体雇员信息，除去雇员SCOTT，以及BLAKE领导的一支。


	


	SCOTT@hyyk> SELECT LPAD(LEVEL,LEVEL*3,' ') as "LEVEL",EMPNO,ENAME,MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR AND ENAME!='SCOTT' and ENAME!='BLAKE' START WITH ENAME='KING';


	

	

	在使用SELECT 语句来报告树结构报表时应当注意，CONNECT BY子句不能作用于出现在WHERE子句中的表连接。如果需要进行连接，可以先用树结构建立一个视图，再将这个视图与其他表连接，以完成所需要的查询。

                                                            

                    

                                                    来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31485142/viewspace-2150578/，如需转载，请注明出处，否则将追究法律责任。
                                            

                
转载于:http://blog.itpub.net/31485142/viewspace-2150578/