http://xxciof.blog.163.com/blog/static/7978132720095193113752/
Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的，其基本语法是：
select ... from tablename start with 条件1
connect by 条件2
where 条件3;
例：
select * from table
start with org_id = 'HBHqfWGWPy'
connect by prior org_id = parent_id;
简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段:
org_id,parent_id那么通过表示每一条记录的parent是谁，就可以形成一个树状结构。
用上述语法的查询可以取得这棵树的所有记录。
其中：
条件1 是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。
条件2 是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR org_id = parent_id就是说上一条记录的org_id 是本条记录的parent_id，即本记录的父亲是上一条记录。
条件3 是过滤条件，用于对返回的所有记录进行过滤。
简单介绍如下：
早扫描树结构表时，需要依此访问树结构的每个节点，一个节点只能访问一次，其访问的步骤如下：
第一步：从根节点开始；
第二步：访问该节点；
第三步：判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步；
第四步：若该节点为叶节点，则访问完毕，否则执行第五步；
第五步：返回到该节点的父节点，并执行第三步骤。
总之：扫描整个树结构的过程也即是中序遍历树的过程。
1． 树结构的描述
树结构的数据存放在表中，数据之间的层次关系即父子关系，通过表中的列与列间的关系来描述，如EMP表中的EMPNO和MGR。EMPNO表示该雇员的编号，MGR表示领导该雇员的人的编号，即子节点的MGR值等于父节点的EMPNO值。在表的每一行中都有一个表示父节点的MGR（除根节点外），通过每个节点的父节点，就可以确定整个树结构。
在SELECT命令中使用CONNECT BY 和蔼START WITH 子句可以查询表中的树型结构关系。其命令格式如下：
SELECT 。。。
CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 列名2}
[START WITH]；
其中：CONNECT BY子句说明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的关系中。PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，PRIOR运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。
START WITH： 不但可以指定一个根节点，还可以指定多个根节点。
2． 关于PRIOR
运算符PRIOR被放置于等号前后的位置，决定着查询时的检索顺序。
PRIOR被置于CONNECT BY子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式。如：
CONNECT BY PRIOR EMPNO=MGR
PIROR运算符被置于CONNECT BY 子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向上的方式。例如：CONNECT BY EMPNO=PRIOR MGR
在这种方式中也应指定一个开始的节点。
3． 定义查找起始节点
在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。
4．使用LEVEL
在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2， 依此类推。
5．节点和分支的裁剪
在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。
6．排序显示
像在其它查询中一样，在树结构查询中也可以使用ORDER BY 子句，改变查询结果的显示顺序，而不必按照遍历树结构的顺序
=======================================================
扩展：
层次化查询,即树型结构查询,是SQL中经常用到的功能之一,通常由根节点,父节点,子节点,叶节点组成,其语法如下:
SELECT [LEVEL] ,column,expression,...
FROM table_name
[WHERE where_clause]
[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];
LEVEL:为伪列,用于表示树的层次
start_condition：层次化查询的起始条件
prior_condition：定义父节点和子节点之间的关系
--使用start with ...connect by prior 从根节点开始遍历
select empno,mgr,ename,job from emp
start with empno = 7839
connect by prior empno = mgr;
EMPNO MGR ENAME JOB
---------- ---------- ---------- ---------
7839 KING PRESIDENT
7566 7839 JONES MANAGER
7788 7566 SCOTT ANALYST
7876 7788 ADAMS CLERK
7902 7566 FORD ANALYST
7369 7902 SMITH CLERK
7698 7839 BLAKE MANAGER
7499 7698 ALLEN SALESMAN
7521 7698 WARD SALESMAN
7654 7698 MARTIN SALESMAN
7844 7698 TURNER SALESMAN
EMPNO MGR ENAME JOB
---------- ---------- ---------- ---------
7900 7698 JAMES CLERK
7782 7839 CLARK MANAGER
7934 7782 MILLER CLERK
14 rows selected.
树型结构遍历过程(通过上面的查询来描述)
1).从根节点开始(即where_clause中的条件,如果为非根节点则分根节点作为根节点开始遍历,如上例empno = 7839)
2).遍历根节点(得到empno = 7839记录的相关信息)
3).判断该节点是否存在有子节点，如果有，则访问最左侧未被访问的子节点,转到1，否则下一步
如上例中prior_condition为empno = mgr,即子节点的mgr等于父节点的empno,在此时mgr为7839的记录
4).当节点为叶节点，则访问完毕,否则,转到3
5).返回到该节点的父节点,转到3)
--伪列level的使用
--注意connect by prior empno = mgr 的理解
--prior表示前一条记录,即下一条返回记录的mgr应当等于前一条记录的empno
select level,empno,mgr,ename,job from emp
start with ename = 'KING'
connect by prior empno = mgr
order by level;
LEVEL EMPNO MGR ENAME JOB
---------- ---------- ---------- ---------- ---------
1 7839 KING PRESIDENT
2 7566 7839 JONES MANAGER
2 7698 7839 BLAKE MANAGER
2 7782 7839 CLARK MANAGER
3 7902 7566 FORD ANALYST
3 7521 7698 WARD SALESMAN
3 7900 7698 JAMES CLERK
3 7934 7782 MILLER CLERK
3 7499 7698 ALLEN SALESMAN
3 7788 7566 SCOTT ANALYST
3 7654 7698 MARTIN SALESMAN
LEVEL EMPNO MGR ENAME JOB
---------- ---------- ---------- ---------- ---------
3 7844 7698 TURNER SALESMAN
4 7876 7788 ADAMS CLERK
4 7369 7902 SMITH CLERK
--获得层次数
select count(distinct level) "Level" from emp
start with ename = 'KING'
connect by prior empno = mgr;
Level
----------
4
--格式化层次查询结果(使用左填充* level - 1个空格)
col Ename for a30
select level,lpad(' ',2 * level - 1) || ename as "Ename",job from emp
start with ename = 'KING' connect by prior empno = mgr;
LEVEL Ename JOB
---------- ------------------------------ ---------
1 KING PRESIDENT
2 JONES MANAGER
3 SCOTT ANALYST
4 ADAMS CLERK
3 FORD ANALYST
4 SMITH CLERK
2 BLAKE MANAGER
3 ALLEN SALESMAN
3 WARD SALESMAN
3 MARTIN SALESMAN
3 TURNER SALESMAN
LEVEL Ename JOB
---------- ------------------------------ ---------
3 JAMES CLERK
2 CLARK MANAGER
3 MILLER CLERK
14 rows selected.
--从非根节点开始遍历(只需修改start with 中的条件即可)
select level,
lpad(' ',2 * level - 1) || ename as "Ename",
job
from emp
start with ename = 'SCOTT'
connect by prior empno = mgr;
LEVEL Ename JOB
---------- ------------------------------ ---------
1 SCOTT ANALYST
2 ADAMS CLERK
--从下向上遍历(交换connect by prior中的条件即可,使用mgr = empno)
--注意connect by prior mgr = empno 的理解
--prior表示前一条记录,即下一条返回记录的empno应当等于前一条记录的mgr
select level,
lpad(' ',2 * level - 1) || ename as "Ename",
job
from emp
start with ename = 'SCOTT'
connect by prior mgr = empno;
LEVEL Ename JOB
---------- ------------------------------ ---------
1 SCOTT ANALYST
2 JONES MANAGER
3 KING PRESIDENT
--从下向上遍历(也可以将prior置于等号右边,得到相同的结果)
select level,
lpad(' ',2 * level - 1) || ename as "Ename",
job
from emp
start with ename = 'SCOTT'
connect by empno = prior mgr;
LEVEL Ename JOB
---------- ------------------------------ ---------
1 SCOTT ANALYST
2 JONES MANAGER
3 KING PRESIDENT
--从层次查询中删除节点和分支
select level,
lpad(' ',2 * level - 1) || ename as "Ename"
,job
from emp
where ename != 'SCOTT' --通过where子句来过滤SCOTT用户，但SCOTT的下属ADAMS并没有过滤掉
start with empno = 7839
connect by prior empno = mgr;
LEVEL Ename JOB
---------- -------------------- ---------
1 KING PRESIDENT
2 JONES MANAGER
4 ADAMS CLERK
3 FORD ANALYST
4 SMITH CLERK
2 BLAKE MANAGER
3 ALLEN SALESMAN
3 WARD SALESMAN
3 MARTIN SALESMAN
3 TURNER SALESMAN
3 JAMES CLERK
LEVEL Ename JOB
---------- -------------------- ---------
2 CLARK MANAGER
3 MILLER CLERK
13 rows selected.
--通过将过滤条件由where 子句的内容移动到connect by prior 子句中过滤掉SCOTT及其下属
select level,
lpad(' ',2 * level - 1) || ename as "Ename"
,job
from emp
start with empno = 7839
connect by prior empno = mgr and ename != 'SCOTT';
LEVEL Ename JOB
---------- -------------------- ---------
1 KING PRESIDENT
2 JONES MANAGER
3 FORD ANALYS
4 SMITH CLERK
2 BLAKE MANAGER
3 ALLEN SALESMAN
3 WARD SALESMA
3 MARTIN SALESMAN
3 TURNER SALESMAN
3 JAMES CLERK
2 CLARK MANAGER
LEVEL Ename JOB
---------- -------------------- ---------
3 MILLER CLERK
12 rows selected.
--在层次化查询中增加过滤条件或使用子查询
select level,
lpad(' ',2 * level - 1) || ename as "Ename"
,job
from emp
where sal > 2500
start with empno = 7839
connect by prior empno = mgr
;
LEVEL Ename JOB
---------- -------------------- ---------
1 KING PRESIDENT
2 JONES MANAGER
3 SCOTT ANALYST
3 FORD ANALYST
2 BLAKE MANAGER
SQL>
select level,
lpad(' ',2 * level - 1) || ename as "Ename"
,job
from emp
where sal > (select avg(sal) from emp)
start with empno = 7839
connect by prior empno = mgr ;
LEVEL Ename JOB
---------- -------------------- ---------
1 KING PRESIDENT
2 JONES MANAGER
3 SCOTT ANALYST
3 FORD ANALYST
2 BLAKE MANAGER
2 CLARK MANAGER
6 rows selected.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31397003/viewspace-2143735/ ，如需转载，请注明出处，否则将追究法律责任。
