ConnectBy、Level、StartWith的使⽤ORACLE--Connect By、Level、Start With的使⽤(Hierarchical query-层次查询)
Syntax 1CONNECT BY [NOCYCLE] <condition> START WITH <condition>
Syntax 2START WITH <condition> CONNECT BY [NOCYCLE] <condition>
参考⽹址:acle/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:489772591421
/reference/connectby.html
adev/connect_by.jsp
spun/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
connect to和connect with的区别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'
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 pno != 7566
7 CONNECT BY PRIOR E.EMPNO = E.MGR8 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.
I believe that we are who we choose to be. Nobody‘s going to come and save you, you‘ve got to save yourself. 我相信我们成为怎样的⼈是我们⾃⼰的选择。没有⼈会来拯救你，你必须要⾃⼰拯救⾃⼰。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论