转自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