转:http://blog.itpub.net/519536/viewspace-624176
有关层次查询之前的文章参考如下。
【层次查询】Hierarchical Queries之“树的遍历”
http://space.itpub.net/519536/viewspace-623809
【层次查询】Hierarchical Queries之LEVEL应用
http://space.itpub.net/519536/viewspace-623916
【层次查询】Hierarchical Queries之CONNECT_BY_ISCYCLE伪列
http://space.itpub.net/519536/viewspace-624032
【层次查询】Hierarchical Queries之CONNECT_BY_ISLEAF伪列
http://space.itpub.net/519536/viewspace-624075
【层次查询】Hierarchical Queries之SYS_CONNECT_BY_PATH函数
http://space.itpub.net/519536/viewspace-624099
【层次查询】Hierarchical Queries之寻根问祖(CONNECT_BY_ROOT一元运算符)
http://space.itpub.net/519536/viewspace-624114
在层次查询中,如果想让“亲兄弟”按规矩进行升序排序就不得不借助ORDER SIBLINGS BY这个特定的排序语句,若要降序输出可以在其后添加DESC关键字。
通过这个实验给大家展示一下这个“亲兄弟”间的排序功能。
1.回望关系“树”,这棵树中的B和C是亲兄弟,F和G是亲兄弟。注意,D和E不是亲兄弟,最多也就算是堂兄弟,“亲兄弟排序”功能对他们无效。
A
/ \
B C
/ /
D E
/ \
F G
2.重温一下阐述上图的T表数据
sec@ora10g> select * from t;
X Y Z
---------- ---------- ----------
A 1
B 2 1
C 3 1
D 4 2
E 5 3
F 6 4
G 7 4
7 rows selected.
3.我们对X列使用“ORDER SIBLINGS BY”进行升序排序,重点关注B和C、F和G的顺序。
sec@ora10g> select lpad(' ',level-1)||x tree, SYS_CONNECT_BY_PATH(x,'/') tree_path from t start with x = 'A' connect by prior y=z order siblings by x;
TREE TREE_PATH
---------------- ----------------
A /A
B /A/B
D /A/B/D
F /A/B/D/F
G /A/B/D/G
C /A/C
E /A/C/E
7 rows selected.
4.我们对X列使用“ORDER SIBLINGS BY DESC”进行降序排序,重点关注B和C、F和G的顺序。
sec@ora10g> select lpad(' ',level-1)||x tree, SYS_CONNECT_BY_PATH(x,'/') tree_path from t start with x = 'A' connect by prior y=z order siblings by x desc;
TREE TREE_PATH
---------------- ----------------
A /A
C /A/C
E /A/C/E
B /A/B
D /A/B/D
G /A/B/D/G
F /A/B/D/F
7 rows selected.
5.如在层次查询中错误的使用了“ORDER BY”进行排序,则层次查询中蕴含的遍历顺序将被打乱,剩下的将只是信息的罗列。
sec@ora10g> select lpad(' ',level-1)||x tree, SYS_CONNECT_BY_PATH(x,'/') tree_path from t start with x = 'A' connect by prior y=z order by x;
TREE TREE_PATH
---------------- ----------------
A /A
B /A/B
C /A/C
D /A/B/D
E /A/C/E
F /A/B/D/F
G /A/B/D/G
7 rows selected.
6.小结
具体问题具体分析,特殊场合特出方法。在层次查询中如需对亲兄弟的先后顺序进行限定,就必须要使用特有的“ORDER SIBLINGS BY”而非“ORDER BY”子句
相关推荐
This is done by processing nodes of a spatial hierarchy in a front-to-back order and interleaving occlusion queries with rendering of certain previously visible nodes. The proposed scheduling of the ...
hierarchical guanyu R
2010年ICIP会议最新文章关于多目标跟踪
Hierarchical Dirichlet Process简单介绍
Hierarchical Dirichlet Proces HDP 代码 matlab
Hierarchical Clustering And Structuring Of Data With Self-Organizing
在Oracle中,有个很方便的特性,层次关系子句。它可以实现需要递归关联查询的各种情景,Oracle_hierarchical_SQL_查询应用.doc里面给出了几个情景,用详细的sql语句来说明怎么查询出需要的组合信息
Knowledge Graph Embedding with Hierarchical Relation Structure 阅读报告
Hierarchical Graph Models for Conflict Resolution 冲突消解的层次图模型
Chloroplast thylakoid membranes accommodate densely packed protein complexes in ordered, often semi- crystalline arrays and are assembled into highly organized multilamellar systems, an organization ...
Behavior Trees for Hierarchical RTS AI
个人Hierarchical softmax之CBOW推倒+知乎讲解(注明了引用~),看了知乎上大佬的讲解后,自己手动重新推倒了CBOW的loss的设计以及个人理解模型导图和反向传播过程
多层分类Hierarchical softmax模型,王圣,周延泉,本文引入了一个新的结构来进行多层分类,它是一个多层分类Hierarchical softmax模型。以往的多层分类问题不能用一个Hierarchical softmax模型��
车辆检测
MartinPelikan HierarchicalBayesian Optimization Algorithm Towarda New Generation of Evolutionary Algorithms
develops a useful correspondence between any hierarchical system of such clusters, and a particular type of distance measure. The correspondence gives rise to two methods of clustering that are ...
关于层次聚类(hierarchical clustering)的基本步骤: 1、假设每个样本为一类,计算每个类的距离,也就是相似度 2、把最近的两个合为一新类,这样类别数量就少了一个 3、重新新类与各个旧类(去了那两个合并的类)之间...
Bayesian Disease Mapping - Hierarchical Modeling in Spatial Epidemiology
A Hierarchical Neural Autoencoder for Paragraphs and Documents
managing-hierarchical-data-in-mysql.rar