database – 具有where子句举动的分层查询“START WITH”

我在事变时代碰着了一个查询,无法弄清晰它是怎样事变的.查询的浸染是查找本日作为其怙恃的全部怙恃.

此刻的诀窍是每个父子相关都有一个有用的一连时刻.

以此数据集为参考：

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

要获取列表,早年我们行使过这个SQL：

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;

Here is a sqlfiddle demo

感激@ 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;

Another sqlfiddle demo

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!