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

（编辑：衡阳站长网）

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