http://philip.greenspun.com/sql/trees.html

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

"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).

 1 WITH A AS
2  (SELECT MAX(LEVEL) + 1 LVL
3     FROM EMP E
4   CONNECT BY PRIOR E.MGR = E.EMPNO
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

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
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.

1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5

1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5

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

 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
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
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 是字符型或能自动转

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;

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;

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
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.

I believe that we are who we choose to be. Nobody‘s going to come and save you, you‘ve got to save yourself. 我相信我们成为怎样的人是我们自己的选择。没有人会来拯救你，你必须要自己拯救自己。

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客