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