database - 具有 where 子句行为的分层查询 "START WITH"

Question

我在工作中遇到了一个查询，但无法弄清楚它究竟是如何工作的。查询的作用是查找今天作为其 parent 的某个人的所有 parent 。

现在这里的诀窍是每个父子关系都有一个有效的持续时间。

以此数据集为引用:

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 今天有效的 parent 名单应该只包含 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 是绑定(bind)变量

此查询无效，因为 where 子句的行为是先获取所有记录，然后检查非连接条件(检查开始日期和结束日期)。这导致它给出的 parent 列表为 NewFather, GrandParent，这是完全错误的!

因此，查询更改为以下内容:

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 = (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，其中只有 per_id 列用于 中的每个人>ci_per_per.

Can we do better? Is a cleaner approach available for the fixing the original query?

更新

此查询仅适用于在层次结构中更高级别的旅行，而不适用于我们正在寻找 child 的情况。但是，此查询从不寻找 child ，也不应该这样做。

Answer 1

我不确定我是否理解正确，但为什么不:

SELECT connect_by_root per_id2 AS per_id2,
       pp.per_id1,
       LEVEL                   AS per_level,
       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 无关)你仍然需要 < strong>不是开始。
换句话说，它不是从“ child ”开始的，而是从“ child ”的父亲——“父亲”和“新父亲”开始的。

因此，要么使用@user1395 建议在 connect by 子句中使用日期逻辑以在父亲不相关时停止，并使用 start with 子句来制作只有相关的父亲可用，或者首先删除所有不相关的父亲(如我之前的建议)或“从”“ child ”而不是它的父亲开始:

select * from (
SELECT connect_by_root per_id1 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       
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