ORACLE--Connect By、Level、Start With的使用(Hierarchical query-层次查询)

Syntax 1	CONNECT BY [NOCYCLE] <condition> START WITH <condition>
Syntax 2	START WITH <condition> CONNECT BY [NOCYCLE] <condition>

参考网址:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:489772591421

            http://psoug.org/reference/connectby.html

            http://www.oradev.com/connect_by.jsp

           http://philip.greenspun.com/sql/trees.html
查找员工编号为7369的领导:

1 SELECT LEVEL,E.* FROM EMP E CONNECT BY PRIOR E.MGR = E.EMPNO  START WITH E.EMPNO = 7876
2 ORDER BY LEVEL DESC

"start with" -- this identifies all LEVEL=1 nodes in the tree

"connect by" -- describes how to walk from the parent nodes above to their children and
their childrens children.

Easiest to use an example on emp. If we start with "where mgr is NULL", we generate the
set of employees that have no mgr (they are the top of the tree). If we

CONNECT BY PRIOR EMPNO = /* current */ MGR

that will take all of the PRIOR records (the start with at first) and find all records
such that the MGR column equals their EMPNO (find all the records of people managed by
the people we started with).

使用WITH语句优化查询结果:优化等级

 1 WITH A AS
 2  (SELECT MAX(LEVEL) + 1 LVL
 3     FROM EMP E
 4   CONNECT BY PRIOR E.MGR = E.EMPNO
 5    START WITH E.EMPNO = 7876
 6    ORDER BY LEVEL DESC)
 7 SELECT A.LVL 最高等级加1,
 8        LEVEL 当前等级,
 9        A.LVL - LEVEL 优化后等级,
10        E.* 　FROM A,
11        EMP E CONNECT BY PRIOR E.MGR = E.EMPNO START WITH E.EMPNO = 7876 ORDER BY LEVEL DESC

查找员工编号为7839的所有下属(7839为king):

1 SELECT LEVEL 等级, E.*
2   FROM EMP E
3 CONNECT BY PRIOR E.EMPNO = E.MGR
4  START WITH E.EMPNO = 7839

--构造整个的层次结构

1 select lpad(' ',level*2,' ')||ename ename, empno, mgr
2     from emp
3     START WITH MGR IS NULL
4     CONNECT BY PRIOR EMPNO = MGR

So, KING is the start with set then JONES BLAKE and CLARK fall under him. Each of them
becomes the PRIOR record in turn and their trees are expanded.

使用Connect By 结合 level构造虚拟行:

1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5

使用rownum实现类似的功能:

1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5

---------------------待续-----------------------

使用UNION ALL构造两层节点的树:

视图如下所示:

 1 CREATE OR REPLACE VIEW TREE_VIEW AS
 2 SELECT
 3  '1' AS rootnodeid,
 4  'xxxx有限责任公司' AS treename,
 5  '-1'  AS parent_id
 6 FROM dual
 7 UNION
 8 SELECT
 9   to_char(d.deptno),
10   d.dname || '_' ||d.loc,
11   '1' AS parent_id
12  FROM dept d;

查询语句:

1 SELECT T.*, LEVEL
2   FROM TREE_VIEW T
3  START WITH T.PARENT_ID = '-1'
4 CONNECT BY PRIOR T.ROOTNODEID = T.PARENT_ID

-----以下为更新内容:

1、先查看总共有几个等级：

1 SELECT COUNT(LEVEL)
2   FROM EMP E
3 CONNECT BY PRIOR E.EMPNO = E.MGR
4  START WITH E.MGR IS NULL;

2、查看每个等级的人数。主要是通过LEVEL进行GROUP BY

1 SELECT COUNT(LEVEL)
2   FROM EMP E
3 CONNECT BY PRIOR E.EMPNO = E.MGR
4  START WITH E.MGR IS NULL
5  GROUP BY LEVEL;

3、Oracle 10g提供了一个简单的connect_by_isleaf=1,

0 表示非叶子节点

1 SELECT LEVEL AS 等级, CONNECT_BY_ISLEAF AS 是否是叶子节点, E.*
2   FROM EMP E
3 CONNECT BY PRIOR E.EMPNO = E.MGR
4  START WITH E.MGR IS NULL

4、SYS_CONNECT_BY_PATH

Oracle 9i提供了sys_connect_by_path(column,char),其中column 是字符型或能自动转

换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。这个函数只能使用在层次查询中。

1 SELECT LEVEL AS 等级,
2        CONNECT_BY_ISLEAF AS 是否是叶子节点,
3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>')
4   FROM EMP E
5 CONNECT BY PRIOR E.EMPNO = E.MGR
6  START WITH E.MGR IS NULL;

5、修剪树枝和节点:

    过滤掉编号是7566的数据(修剪节点)，他指的是把这个节点给裁掉，但是并没有破坏树结构，它的子节点还是可以正常的显示。

1 SELECT LEVEL AS 等级,
2        CONNECT_BY_ISLEAF AS 是否是叶子节点,
3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
4        E.*
5   FROM EMP E
6 WHERE e.empno != 7566
7 CONNECT BY PRIOR E.EMPNO = E.MGR
8  START WITH E.MGR IS NULL;

裁掉编号是7698的节点和它的子节点:

1 SELECT LEVEL AS 等级,
2        CONNECT_BY_ISLEAF AS 是否是叶子节点,
3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
4        E.*
5   FROM EMP E
6 CONNECT BY PRIOR E.EMPNO = E.MGR
7        AND E.EMPNO != 7698
8  START WITH E.MGR IS NULL;

6、CONNECT_BY_ROOT的使用，oracle10g新增connect_by_root,用在列名之前表示此行的根节点的相同列名的值。

1 SELECT LEVEL AS 等级,
2        CONNECT_BY_ISLEAF AS 是否是叶子节点,
3        CONNECT_BY_ROOT ENAME,
4        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
5        E.*
6   FROM EMP E
7 CONNECT BY PRIOR E.EMPNO = E.MGR
8  START WITH E.MGR IS NULL;

 

对于层次查询如果用order by排序，比如order by last_name则是先做完层次获得level,然后按last_name 排序，这样破坏了层次，比如特别关注某行的深度，按level 排序，也是会破坏层次的。在oracle10g中，增加了siblings 关键字的排序。

语法：order siblings by <expre>

它会保护层次，并且在每个等级中按expre排序。

1 SELECT LEVEL AS 等级,
2        CONNECT_BY_ISLEAF AS 是否是叶子节点,
3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
4        E.*
5   FROM EMP E
6 CONNECT BY PRIOR E.EMPNO = E.MGR 
7  START WITH E.MGR IS NULL
8  ORDER SIBLINGS BY  E.ENAME;

connect_by_iscycle(存在循环，将返回1，否则返回0)

The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.
You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.