在Oracle CONNECT BY分层查询中引用父列
我有这样的数据：
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键标识的'object'。 可以有任何级别的嵌套。 （请注意，只要保留层次结构， 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字段都定义了记录的完整组合键）。
（PS使用Oracle 10gR2，如果它有所作为）。
只需使用：
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
(...)
我假定你的帖子归结为这样的问题：“我如何在分层查询中引用父行的FLAG属性？”
我不知道我提出的SQL是正确的。 如果不是，我很抱歉。 但总的来说，这是我的方法：
在层次结构中的每个级别，我将所有密钥（ SYS_CONNECT_BY_PATH ）串在一起。 然后用SUBSTR ， INSTR和LEVEL ，我SUBSTR inged了什么相当于父级重点。 最后，在PARENT_FLAG的定义中，我选择了一个行的FLAG ，该行的键与这个SUBSTR inged-out键匹配。
建立：
CREATE TABLE tree (
key1 VARCHAR2(5)
, key2 VARCHAR2(10)
, key3 VARCHAR2(5)
, lkey1 VARCHAR2(5)
, lkey2 VARCHAR2(10)
, lkey3 VARCHAR2(5)
, flag VARCHAR2(1)
);
INSERT INTO tree VALUES ('09/10','10000','A1234','09/10','AU000123','A1234','1');
INSERT INTO tree VALUES ('09/10','10000','A1234','09/10','AU000456','A1234','1');
INSERT INTO tree VALUES ('09/10','10000','A1234','09/10','AX000001','A1234','1');
INSERT INTO tree VALUES ('09/10','AX000001','A1234','09/10','AE000010','A1234','0');
INSERT INTO tree VALUES ('09/10','AX000001','A1234','09/10','AE000020','A1234','0');
INSERT INTO tree VALUES ('09/10','AX000001','A1234','09/10','AE000030','A1234','0');
INSERT INTO tree VALUES ('09/10','10000','A1234','09/10','AX000002','A1234','0');
INSERT INTO tree VALUES ('09/10','AX000002','A1234','09/10','AE000040','A1234','0');
INSERT INTO tree VALUES ('09/10','10000','A1234','09/10','AU000789','A1234','0');
查询：
COL flag FOR A4
COL same_length FOR A11
COL has_x_2nd FOR A9
COL full_key_path FOR A50
COL parent_key FOR A30
COL parent_flag FOR A11
WITH querykeys AS (
SELECT '09/10' key1
, '10000' key2
, 'A1234' key3
FROM DUAL
)
, subtree1 AS (
SELECT tree.key1
, tree.key2
, tree.key3
, tree.lkey1
, tree.lkey2
, tree.lkey3
, tree.flag
, CONNECT_BY_ISLEAF isleaf
, LEVEL thislevel
, DECODE(LENGTH(tree.key2)
, LENGTH(tree.lkey2), '1'
, '0') same_length
, DECODE(UPPER(SUBSTR(tree.key2,2,1))
, 'X', '1'
, '0') has_x_2nd
, SYS_CONNECT_BY_PATH(tree.key1 || '|' || tree.key2 || '|' || tree.key3,'')
|| ''
|| tree.lkey1 || '|' || tree.lkey2 || '|' || tree.lkey3 || '' full_key_path
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
)
, subtree2 AS (
SELECT st1.key1
, st1.key2
, st1.key3
, st1.lkey1
, st1.lkey2
, st1.lkey3
, st1.flag
, st1.isleaf
, st1.thislevel
, st1.same_length
, st1.has_x_2nd
, st1.full_key_path
, SUBSTR(st1.full_key_path
, INSTR(st1.full_key_path,'',1,st1.thislevel) + 1
, INSTR(st1.full_key_path,'',1,st1.thislevel + 1)
- INSTR(st1.full_key_path,'',1,st1.thislevel) - 1) parent_key
FROM subtree1 st1
)
SELECT st2.key1
, st2.key2
, st2.key3
, st2.lkey1
, st2.lkey2
, st2.lkey3
, st2.flag
, st2.isleaf
, st2.thislevel
, st2.same_length
, st2.has_x_2nd
, (SELECT t_prime.flag
FROM tree t_prime
WHERE t_prime.key1 = SUBSTR(st2.parent_key
, 1
, INSTR(st2.parent_key,'|',1,1) - 1)
AND t_prime.key2 = SUBSTR(st2.parent_key
, INSTR(st2.parent_key,'|',1,1) + 1
, INSTR(st2.parent_key,'|',1,2)
- INSTR(st2.parent_key,'|',1,1) - 1)
AND t_prime.key3 = SUBSTR(st2.parent_key
, INSTR(st2.parent_key,'|',1,2) + 1)
-- Following assumes all rows with parent keys have same flag value.
-- Avoids ORA-01427: single-row subquery returns more than one row.
AND ROWNUM = 1) parent_flag
FROM subtree2 st2
;
结果：
KEY1 KEY2 KEY3 LKEY1 LKEY2 LKEY3 FLAG ISLEAF THISLEVEL SAME_LENGTH HAS_X_2ND PARENT_FLAG
----- ---------- ----- ----- ---------- ----- ---- ---------- ---------- ----------- --------- -----------
09/10 10000 A1234 09/10 AU000123 A1234 1 1 1 0 0 1
09/10 10000 A1234 09/10 AU000456 A1234 1 1 1 0 0 1
09/10 10000 A1234 09/10 AU000789 A1234 0 1 1 0 0 1
09/10 10000 A1234 09/10 AX000001 A1234 1 0 1 0 0 1
09/10 AX000001 A1234 09/10 AE000010 A1234 0 1 2 1 1 0
09/10 AX000001 A1234 09/10 AE000020 A1234 0 1 2 1 1 0
09/10 AX000001 A1234 09/10 AE000030 A1234 0 1 2 1 1 0
09/10 10000 A1234 09/10 AX000002 A1234 0 0 1 0 0 1
09/10 AX000002 A1234 09/10 AE000040 A1234 0 1 2 1 1 0
9 rows selected.
SQL>
正如我所说的，我不是100％肯定我完全赞成你的数据模型，但我希望你能够遵循我的方法。
链接地址: http://www.djcxy.com/p/93931.html上一篇: Reference to parent columns in Oracle CONNECT BY hierarchical query