现在的诀窍是每个父子关系都有一个有效的持续时间.
以此数据集为参考：
GrandParent is parent of Father from 01-01-2012 to 02-02-2015
Father is parent of Child from 01-01-2012 to 02-02-2011
Child is just the lowest level person
NewFather is parent of Child from 01-01-2012 to 02-02-2014
现在,今天对于Child有效的父母列表应该只包含NewFather
SELECT connect_by_root per_id2 AS per_id2,per_id1,LEVEL AS per_level,n.entity_name
FROM ci_per_per pp,ci_per_name N
WHERE N.per_id = per_id1
AND start_dt <= SYSDATE
AND ( end_dt IS NULL
OR end_dt >= SYSDATE )
START WITH per_id2 = :personID
CONNECT BY NOCYCLE PRIOR per_id1 = per_id2;
其中personID是绑定变量
此查询不起作用,因为where子句行为是这样的,它首先获取所有记录,然后检查非连接条件(检查开始日期和结束日期).这导致它给出父亲列表NewFather,GrandParent完全错误！
SELECT connect_by_root per_id2 AS per_id2,ci_per_name N
WHERE N.per_id = per_id1
AND start_dt <= SYSDATE
AND ( end_dt IS NULL
OR end_dt >= SYSDATE )
START WITH per_id2 = (SELECT per_id
FROM ci_acct_per
WHERE per_id = :personID
AND pp.start_dt <= SYSDATE
AND ( pp.end_dt IS NULL
OR pp.end_dt >= SYSDATE ))
CONNECT BY NOCYCLE PRIOR per_id1 = per_id2;
现在我不明白的是：
how can a where condition in the start with clause affect the behavior of the query in such a manner?
我不喜欢这个查询的另一件事是它使用一个名为ci_acct_per的完全不相关的表,它只为ci_per_per中的每个人都有一列per_id.
Can we do better? Is a cleaner approach available for the fixing the original query?
UPDATE
解决方法
SELECT connect_by_root per_id2 AS per_id2,pp.per_id1,n.entity_name
FROM (select *
from ci_per_per
where start_dt <= SYSDATE
AND ( end_dt IS NULL
OR end_dt >= SYSDATE )) pp join
ci_per_name N on N.per_id = pp.per_id1
START WITH per_id2 = 1
CONNECT BY NOCYCLE PRIOR pp.per_id1 = pp.per_id2;
感谢@ user1395 example更新：
很难解释奇怪的查询是如何工作的,因为它没有……
真正发生的是START WITH子句使用per_id2这是“父”列,所以如果有多个(一个与sysdate无关),你仍然不需要从它开始.
换句话说,它不是从“孩子”开始,而是从“孩子”父亲 – “父亲”和“新父”开始.
因此,要么使用@ user1395建议在connect by子句中都有日期逻辑,要么在父亲不相关时停止,并且从子句开始只使相关父亲可用,或者首先删除所有不相关的父亲(如在我以前的建议中)或“从”开始“孩子”,而不是它的父亲：
select * from (
SELECT connect_by_root per_id1 AS per_id2,ci_per_name N
WHERE N.per_id = per_id1
START WITH per_id1 = 1
CONNECT BY NOCYCLE PRIOR per_id1 = per_id2 AND start_dt <= SYSDATE
AND ( end_dt IS NULL
OR end_dt >= SYSDATE ))
where per_id1 <> per_id2;
