# [转]层次查询-亲兄弟间的排序ORDER SIBLINGS BY

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

• 博文量
106
• 访问量
209536