由Oracle分层查询中的PRIOR语句放置引起的差异

斯马肯齐

似乎在使用Connect By时可以在Oracle中执行以下两项操作。

 CONNECT BY NOCYCLE 
    parent_id = PRIOR child_r_object_id

 CONNECT BY NOCYCLE PRIOR 
    parent_id = child_r_object_id

有什么区别,大多数在线示例倾向于使用第二种语法,但是都执行。

温弗里德(Wernfried Domscheit)

基本上,您定义从上到下或从上到下构建层次结构。

看下面的例子,看看有什么区别:

WITH t(person, parent_id, ID) AS (
    SELECT 'Grandma', NULL, 1 FROM dual
    UNION ALL SELECT 'Mother', 1, 10 FROM dual
    UNION ALL SELECT 'Daughter', 10, 100 FROM dual
    UNION ALL SELECT 'Son', 10, 101 FROM dual)
SELECT person AS leaf_person, CONNECT_BY_ROOT(person) AS top_person, 
       SYS_CONNECT_BY_PATH(person, '->'), 'down' AS direction
FROM t
WHERE CONNECT_BY_ISLEAF = 1
START WITH parent_id IS NULL
CONNECT BY parent_id = PRIOR ID
UNION ALL
SELECT person as leaf_person, CONNECT_BY_ROOT(person) as top_person, 
       SYS_CONNECT_BY_PATH(person, '->'), 'up' AS direction
FROM t
WHERE CONNECT_BY_ISLEAF = 1
START WITH ID IN (100,101)
CONNECT BY PRIOR parent_id = ID;


+-----------------------------------------------------------------+
|LEAF_PERSON|TOP_PERSON|SYS_CONNECT_BY_PATH(PERSON,'->')|DIRECTION|
+-----------------------------------------------------------------+
|Daughter   |Grandma   |->Grandma->Mother->Daughter     |down     |
|Son        |Grandma   |->Grandma->Mother->Son          |down     |
|Grandma    |Daughter  |->Daughter->Mother->Grandma     |up       |
|Grandma    |Son       |->Son->Mother->Grandma          |up       |
+-----------------------------------------------------------------+

通常,您只有一个根(即parent_id IS NULL)或至少确定的根元素,因此大多数示例使用“从上到下”的方向。

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章