oracle: OCA-047-题解与实验(5) --start with 与 connect by的用法

最近忙啊，酷币的银啊！

题目：

答案解释：关于start with 与 connect by的内容，这两个词语核心功能是：生成树形结果，而题意就是生成树形结果。

这里呢，咱不用题目中的例子，用oracle提供的示例数据库例子，对应employees这个表，估计上面的exhibit也是选取示例中的4列而生成的。看查询示例：

示例一：

SQL> select t.employee_id,t.manager_id, t.last_name from employees t
  2  start with t.manager_id = 100
  3  connect by  prior t.employee_id = t.manager_id;
 
EMPLOYEE_ID MANAGER_ID LAST_NAME
----------- ---------- -------------------------
        101        100 Kochhar                  这条结果是第一个manager_id=100的记录,子节点101的父节点是100  
        108        101 Greenberg                子节点108的父节点是101，可见是将上一条的101作为父节点向下找子节点的
         109        108 Faviet                   同理108的子节点是谁呢？是109
        110        108 Chen                     109有子节点么？显然没有，所以继续找108的其他子节点，找到了110
        111        108 Sciarra                  110有子节点么？显然没有，所以继续找108的其他子节点，找到了111
        112        108 Urman                    111有子节点么？显然没有，所以继续找108的其他子节点，找到了112      
        113        108 Popp                     112有子节点么？显然没有，所以继续找108的其他子节点，找到了113
        200        101 Whalen                   同上继续找108其他子节点，发现没有，返回108的父节点101，找到101其他子节点200 
        203        101 Mavris                   200有子节点么？显然没有，所以继续找101的其他子节点，找到了203 
        204        101 Baer                     以下就是重复了....
        205        101 Higgins                  ...
        206        205 Gietz                    ... 
        102        100 De Haan                  ... 
        103        102 Hunold

到这里，能否总结出规律吧：

1、遍历是自上而下的（假设父节点在上层），从父节点开始向子节点开始遍历，只要有子节点就以该子节点为父节点继续遍历，直到没有子节点了返回上一层找其他子节点。

2、哪个数值作为第一个父节点呢，start with指定的就是，随机选取一个满足的记录就开始遍历了。记住，该语句的核心就是返回一个树形结构。树形结构总体是由两个列值组成的如，manager_id和employee_id，物理上分两列，但实际上由于对应关系都是这个树上的节点元素。虽然有完整的树，但可以截取需求的树，而树根就是start with指定的。如start with指定了总老板的id即完整树的根，那么返回的就是整个树遍历结果。选取某个节点，返回的就是部分树的遍历结果。

3、 prior是什么作用？ 下面看：

 示例二：

SQL> select t.employee_id,t.manager_id, t.last_name from employees t
  2  start with t.manager_id = 100
  3  connect by t.employee_id = prior t.manager_id;
 
EMPLOYEE_ID MANAGER_ID LAST_NAME
----------- ---------- -------------------------
        101        100 Kochhar                  101的父节点是100，没话说
         100            King                     100的父节点是NULL？？ 发现了问题，找完了101的父节点又开始找100的父节点了。
         102        100 De Haan                  由于上条记录100已经没有父节点了，又重新回到下层，找到满足语句的102（102的父节点是100） 
         100            King                     但是102的父节点100仍然没有父节点
         114        100 Raphaely                 只好找到114
        100            King
        120        100 Weiss
        100            King
        121        100 Fripp
        100            King
        122        100 Kaufling
        100            King
        123        100 Vollman
        100            King                     一直这么悲剧的下去

什么规律呢？prior其实就是向上遍历了，找到节点后，将该节点的父节点作为子节点，找它的父节点，这就是自下向上遍历了。其他语句因素和上面描述的都一样。

根据上面描述，你能阅读下面内容么？

SQL> select t.employee_id,t.manager_id, t.last_name from employees t
  2  start with t.manager_id = 108
  3  connect by t.employee_id = prior t.manager_id;
 
EMPLOYEE_ID MANAGER_ID LAST_NAME
----------- ---------- -------------------------
        109        108 Faviet
        108        101 Greenberg
        101        100 Kochhar
        100            King
        110        108 Chen
        108        101 Greenberg
        101        100 Kochhar
        100            King

 最后说说 where所起的作用，看下个示例

示例三：

SQL> select t.employee_id,t.manager_id, t.last_name from employees t
  2  where t.last_name like '%an'
  3  start with t.manager_id = 100
  4  connect by prior t.employee_id =  t.manager_id;
 
EMPLOYEE_ID MANAGER_ID LAST_NAME
----------- ---------- -------------------------
        112        108 Urman                    
        102        100 De Haan

对比示例一，可以知道where是对遍历完的树形结构进行筛选，找到满足where条件的所有节点。如题是满足名字以‘an’结尾的雇员。