oracle每日一题-分析函数row

xiaoxiao2021-03-25 158

转自http://www.itpub.net/thread-2084317-1-1.html 感谢newkid

原始出处： http://www.plsqlchallenge.com/ 作者：Kim Berg Hansen 运行环境：SQLPLUS, SERVEROUTPUT已打开我有一张表保存着每个人的姓名，出生日期（DOB），以及父亲： create table qz_men ( man_id integer primary key , name varchar2(10) , dob date , father_id integer references qz_men ) / insert into qz_men values (10, 'Zebadiah', date '1950-01-10', null) / insert into qz_men values (15, 'William' , date '1970-01-15', 10 ) / insert into qz_men values (16, 'Stephen' , date '1972-01-16', 10 ) / insert into qz_men values (20, 'Roger' , date '1990-01-20', 15 ) / insert into qz_men values (21, 'Perry' , date '1992-01-21', 15 ) / insert into qz_men values (25, 'Louis' , date '1993-01-25', 16 ) / insert into qz_men values (26, 'Jerry' , date '1991-01-26', 16 ) / commit / 我想要一张以出生日期排序的清单，显示每个人是他父亲的第几个儿子。哪些选项包含的查询能够产生如下的输出： MAN_ID NAME DOB PARENTAGE ---------- ---------- ---------- -------------------- 10 Zebadiah 1950-01-10 Unknown 15 William 1970-01-15 1. son of Zebadiah 16 Stephen 1972-01-16 2. son of Zebadiah 20 Roger 1990-01-20 1. son of William 26 Jerry 1991-01-26 1. son of Stephen 21 Perry 1992-01-21 2. son of William 25 Louis 1993-01-25 2. son of Stephen (A) select m.man_id , m.name , m.dob , nvl2( m.father_id , row_number() over ( partition by PRIOR m.man_id order by m.dob ) || '. son of ' || PRIOR m.name , 'Unknown' ) as parentage from qz_men m start with m.father_id is null connect by m.father_id = prior m.man_id order by m.dob / (B) select m.man_id , m.name , m.dob , nvl2( m.father_id , row_number() over ( partition by PRIOR m.man_id order SIBLINGS by m.dob ) || '. son of ' || PRIOR m.name , 'Unknown' ) as parentage from qz_men m start with m.father_id is null connect by m.father_id = prior m.man_id order by m.dob / (C) select m.man_id , m.name , m.dob , nvl2( m.father_id , row_number() over ( partition by m.father_id order by m.dob ) || '. son of ' || ( select name from qz_men f where f.man_id = m.father_id ) , 'Unknown' ) as parentage from qz_men m start with m.father_id is null connect by m.father_id = prior m.man_id order by m.dob / (D) select m.man_id , m.name , m.dob , nvl2( m.father_id , row_number() over ( partition by m.father_id order by m.dob ) || '. son of ' || ( select name from qz_men f where f.man_id = m.father_id ) , 'Unknown' ) as parentage from qz_men m order by m.dob / (E) select s2.man_id , s2.name , s2.dob , nvl2( s2.father_id , row_number() over ( partition by s2.father_id order by s2.rn ) || '. son of ' || s2.father_name , 'Unknown' ) as parentage from ( select s1.* , rownum as rn from ( select m.* , prior m.name as father_name from qz_men m start with m.father_id is null connect by m.father_id = prior m.man_id order SIBLINGS by m.dob ) s1 ) s2 order by s2.dob / (F) select s1.man_id , s1.name , s1.dob , nvl2( s1.father_id , row_number() over ( partition by s1.father_id order by s1.rn ) || '. son of ' || s1.father_name , 'Unknown' ) as parentage from ( select m.* , prior m.name as father_name , row_number() over ( order SIBLINGS by m.dob ) as rn from qz_men m start with m.father_id is null connect by m.father_id = prior m.man_id ) s1 order by s1.dob /

A: （推荐）在分析函数row_number中我们按照父亲分区，按照每个父亲的孩子的出生日期排序，这样就给出了我们所需的每个孩子的序号。 B: 在CONNECT BY里面，在ORDER BY中使用SIBLING子句，就可以在保留层次顺序的同时，以特定顺序获得树中的兄弟节点，这常常是很有用的。然而在分析函数中是不允许的，所以这个选项会报错： ORA-30929: ORDER SIBLINGS BY clause not allowed here. 此外，在这个例子中SIBLINGS是不需要的，因为我们是按父亲分区（如同前一选项） C: （不推荐）在分区部分，我们在此处用了FATHER_ID而不是选项A那样的PRIOR用法，这是可以的，会得到相同结果。但是选项A同样用了PRIOR来得到父亲的名字，而此处我们用了一个标量子查询，这样也行，但对于数据库而言是不必要的工作。 D: 如果用了标量子查询而不是PRIOR子句，我们就可以完全去除CONNECT BY，因为所需的结果也不是按层次的顺序排列。选项A用了CONNECT BY来通过PRIOR得到父亲的名字, 这样在遍历树的时候也完成了FATHER_ID的查找。这个选项不需要遍历树，但是在MAN_ID上做查找，这样也许会受益于标量子查询的缓存，这取决于数据。大多数情况下，选项A的性能是最好的，但是取决于树的深度和宽度以及索引情况，在某些特殊情况下这个选项也可能很好。这需要测试。 E: （不推荐）此处我们在两层的内联视图中用了SIBLINGS（因为ROWNUM是在ORDER之前产生的，我们需要两个层次的内联视图）来在RN列中生成我们需要的序号，然后就可以在我们的分析函数中使用。它没有问题，但是对这个例子是小题大做，因为以父亲分区就不需要层次顺序了。对于其它用例，这可能是个有用的技巧，可以用来保留层次的顺序。 F: 这是在尝试将前一选项简化为一个内联视图而不是双层视图，用的是分析函数ROW_NUMBER而不是ROWNUM。ROW_NUMBER经常是ROWNUM的一个很好的替身，但是在这个例子中它行不通，因为分析函数的ORDER BY不允许SIBLINGS子句，会报错： ORA-30929: ORDER SIBLINGS BY clause not allowed here.

基础知识可以看http://blog.csdn.net/zhq200902/article/details/59108118

本题新增知识点：ORDER SIBLINGS BY

原文地址 http://space.itpub.net/519536/viewspace-624176 在层次查询中，如果想让“亲兄弟”按规矩进行升序排序就不得不借助ORDERSIBLINGS 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”子句。切记之。

转载请注明原文地址: https://ju.6miu.com/read-893.html

技术

最新回复(0)