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.
I believe that we are who we choose to be. Nobody‘s going to come and save you, you‘ve got to save yourself. 我相信我们成为怎样的人是我们自己的选择。没有
相关推荐
在Oracle中,有个很方便的特性,层次关系子句。它可以实现需要递归关联查询的各种情景,Oracle_hierarchical_SQL_查询应用.doc里面给出了几个情景,用详细的sql语句来说明怎么查询出需要的组合信息
Use new Oracle Database 11g tools and techniques such as PL/Scope, the PL/SQL hierarchical profiler, and the SecureFiles technology for large objects Build modular PL/SQL applications using ...
关于层次聚类(hierarchical clustering)的基本步骤: 1、假设每个样本为一类,计算每个类的距离,也就是相似度 2、把最近的两个合为一新类,这样类别数量就少了一个 3、重新新类与各个旧类(去了那两个合并的类)之间...
Image-level classification by hierarchical structure learning with visual and semantic similarities
GROUP BY with ROLLUP and CUBE Operators 3-6 ROLLUP Operator 3-7 ROLLUP Operator Example 3-8 CUBE Operator 3-9 CUBE Operator: Example 3-10 GROUPING Function 3-11 GROUPING Function: Example 3-12 ...
Knowledge Graph Embedding with Hierarchical Relation Structure 阅读报告
Hierarchical human-like strategy for aspect-level sentiment classification with sentiment linguistic knowledge and reinforcement learning
多篇关于层次聚类的论文,打包下载,具有一定的学习价值
Hierarchical(MIN)聚类算法,C++实现,IDE采用devC,文件内部包括源代码,测试数据集,聚类图形显示程序。详细解释访问http://blog.csdn.net/k76853/article/details/50440468
hierarchical-clustering-with-python-and-scikit-learn-shopping-data.csv
用层次三角网构建球面 微软研究中心 Indexing the Sphere with the Hierarchical Triangular Mesh
多主体系统的层次颜色Petri网模拟方法研究,李军,兰顺国,介绍了多主体系统模拟的颜色Petri网方法,在一个多主体系统中,每个主体对应一个颜色Petri网系统 ,整个多主体系统对应为一个层次颜�
Hierarchical Clustering And Structuring Of Data With Self-Organizing
managing-hierarchical-data-in-mysql.rar
useful high-level visual features, such as object parts, from unlabeled images of objects and natural scenes. We demonstrate excellent performance on several visual recognition tasks and show that our...
与通过分层贝叶斯进行元学习的现有方法相比,MAML通过使用可扩展的梯度下降过程进行后验推理,自然适用于复杂函数逼近器。此外,将MAML识别为分层贝叶斯提供了一种将算法作为元学习过程进行理解的方法,并提供了利用...
具有多级结构的硅酸镁双层空心纳米纤维的制备以及在水污染物处理方面的应用研究,金仁喜,邢艳,本文采用静电纺丝和水热相结合的方法,制备了具有多级结构的硅酸镁双层空心纳米纤维。通过表征发现,所制备的硅酸镁...
The book is a revision of the classic Mastering Oracle SQL and SQL*Plus by Lex de Haan, and has been updated to cover the very latest developments in Oracle’s version of the SQL query language....
2019-自动化所-Semi-supervised Node Classification via Hierarchical Graph Convolutional Networks-相似点聚超级,先粗后细-rrrr-实习1
具有层次注意隶属度的图嵌入_Graph Embedding with Hierarchical Attentive Membership.pdf