sql - Oracle CONNECT BY 分层查询中对父列的引用

Question

我有这样的数据:

KEY1   KEY2      KEY3   LKEY1  LKEY2     LKEY3  FLAG
====== ========= ====== ====== ========= ====== =====
09/10  10000     A1234  09/10  AU000123  A1234  1
09/10  10000     A1234  09/10  AU000456  A1234  1
09/10  10000     A1234  09/10  AX000001  A1234  1
09/10  AX000001  A1234  09/10  AE000010  A1234  0
09/10  AX000001  A1234  09/10  AE000020  A1234  0
09/10  AX000001  A1234  09/10  AE000030  A1234  0
09/10  10000     A1234  09/10  AX000002  A1234  0
09/10  AX000002  A1234  09/10  AE000040  A1234  0
09/10  10000     A1234  09/10  AU000789  A1234  0

这是分层数据，因此我将查询根组合键(在本例中为 09/10 10000 A1234)； FLAG 字段指的是由 LKEYx 键标识的“对象”。可以有任意数量的嵌套级别。 (请注意，KEY1 和 KEY3 字段不必像上面的示例那样保持不变，只要保留层次结构即可。)

我要检索的是叶子节点，但是如果叶子的父KEY2 与LKEY2 长度相同或包含X 作为第二个字符，然后返回直接父级。在这种情况下，我们还需要将记录标记为可选...所以，像这样:

KEY1   KEY2      KEY3   OPTION  FLAG
====== ========= ====== ======= =====
09/10  AU000123  A1234  0       1
09/10  AU000456  A1234  0       1
09/10  AX000001  A1234  1       1
09/10  AX000002  A1234  1       0
09/10  AU000789  A1234  0       0

我已经编写了一个执行此操作的查询，但它并不漂亮。此外，为了区分可选记录，它假设所有叶节点都在树下的同一层；然而，这不一定是真的。我的查询如下:

with queryKeys as (
  select '09/10' key1,
         '10000' key2,
         'A1234' key3,
  from   dual
),
subTree as (
  select     tree.key1,
             tree.key2,
             tree.key3,

             tree.lkey1,
             tree.lkey2,
             tree.lkey3,

             tree.flag,

             connect_by_isleaf isLeaf,
             level thisLevel

  from       tree,
             queryKeys

  start with tree.key1 = queryKeys.key1
  and        tree.key2 = queryKeys.key2
  and        tree.key3 = queryKeys.key3

  connect by tree.key1 = prior tree.lkey1
  and        tree.key2 = prior tree.lkey2
  and        tree.key3 = prior tree.lkey3
),
maxTree as (
  select max(thisLevel) maxLevel
  from   subTree
)
select lkey1 key1,
       lkey2 key2,
       lkey3 key3,
       1 - isLeaf option,
       flag

from   subTree,
       maxTree
where (isLeaf = 1 or thisLevel = maxLevel - 1)
and   (length(key2) != length(lkey2) or substr(lkey2, 2, 1) != 'X');

queryKeys 的原因是因为它在更大的查询中的其他地方使用并且可以包含多个记录。 maxTree 部分是问题所在，超出了它一般的古怪之处!

现在，这个帖子的标题的原因是因为如果我可以引用父级的 FLAG 字段，这个查询可以变得更简单很多。我尝试了一种 JOIN 方法来实现这个想法——在相关键上将树与自身连接起来——但是，除非我弄错了，否则这会导致一个递归问题，你将不得不一直这样做遍历树以找到正确的父键(因为 KEYx 和 LKEYx 字段定义了记录的完整复合键)。

(附注:使用 Oracle 10gR2，如果它有所不同。)

Answer 1

只需使用:

PRIOR FLAG 

它会给你正是你想要的 - 父行的标志字段。

subTree as (            
select     tree.key1,            
         tree.key2,            
         tree.key3,            
         tree.lkey1,            
         tree.lkey2,            
         tree.lkey3,            
         tree.flag,            
         PRIOR TREE.FLAG PRIOR_FLAG
         connect_by_isleaf isLeaf,            
         level thisLevel            

from       tree,            
         queryKeys 
(...)