logo标签列表

Oracle Connect By Prior 递归查询语法的使用方法

sqldatabaseoracleplsqlrecursive-query
7
假设我在我的Oracle数据库中有以下表格：
ID:    Name:     Parent_ID:
123    a         234
345    b         123
234    c         234
456    d         345
567    e         567
678    f         567

我想要做的是，找到每个“ID”的“终极父ID”（即行，当您基于“Parent_ID”向上递归时，您最终获得“ID = Parent_ID”的行）。
例如，345的父项是123，123的父项是234，234的父项是234（意味着它是链的顶部），因此345的终极父项是234 - 我希望这是有意义的...
因此，我的结果应如下所示：
ID:    Name:     Ult_Parent_ID:    Ult_Parent_Name:
123    a         234               c
345    b         234               c
234    c         234               c
456    d         234               c
567    e         567               e
678    f         567               e

我刚刚了解到Oracle的Connect By语句，所以这对于我来说是全新的。但是我想象中的查询可能需要如下：

SELECT ID, Name, Parent_ID as Ult_Parent_ID, 
   (SELECT Name from MyTable t2 WHERE t2.ID = t1.Parent_ID) as Ult_Parent_Name
FROM MyTable t1
CONNECT BY PRIOR Parent_ID = ID;

现在，就像我之前说的那样，这是我第一次尝试这种SQL查询 - 这个查询不能正常工作（我得到了以下错误：[1]: ORA-01436: CONNECT BY loop in user data并且在SQL编辑器中突出显示表名），而且我也不知道在这种查询中如何使用START WITH子句，但是它的逻辑对我来说似乎是正确的。
请帮助我或指点我正确的方向！！！
谢谢！
1
请解释“不起作用”。你是否收到了任何错误信息或仅仅是错误的结果？ - PM 77-1
抱歉 - 解释不清楚 - 我得到了以下错误 [1]: ORA-01436: CONNECT BY loop in user data，并且在查询中突出显示了表名... 对此感到抱歉... 我现在也会更新问题。 - John Bustos
1
尝试添加NOCYCLE子句并查看结果是否符合您的预期。 - PM 77-1
1
如果您可以控制数据库内容，那么将Parent_ID设置为NULL，即没有父级，可能对您有利。 - PM 77-1
谢谢 @PM77-1 - 是的，感谢您提供的提示，使用nocycle子句可以得到正确的结果... 我仍然不知道如何做到：1）让它适用于所有记录（而不仅仅是检查1条记录），2）让它在没有该子句的情况下工作。但是，是的，数据看起来是正确的。同时，不幸的是，我无法控制数据库 - 我只有查询权限... - John Bustos
4个回答
5

I think the CONNECT_BY_ROOT is what you need:

select x.*, t2.name ultimate_name
from
(
  select t.id, t.name, CONNECT_BY_ROOT parent_id ultimate_id
  from toto t
  start with t.id = t.parent_id
  connect by nocycle prior id = parent_id
) x, toto t2
where x.ultimate_id = t2.id
;

这将会得到：
456 d   234 c
345 b   234 c
123 a   234 c
234 c   234 c
678 f   567 e
567 e   567 e
这个解决了问题！！！非常感谢！！我自己永远不可能想到！！！ - John Bustos
1
请尝试这个：

请尝试这个：

SELECT ID, Name, Parent_ID as Ult_Parent_ID, 
   (SELECT Name from MyTable t2 WHERE t2.ID = t1.Parent_ID) as Ult_Parent_Name, 
   LEVEL
FROM MyTable t1
CONNECT BY NOCYCLE Parent_ID = PRIOR ID
START WITH Parent_ID = ID;

我认为我们必须使用 NOCYCLE，因为你的根据定义的方式。
我添加了伪列 LEVEL 只是为了说明目的。在最终查询中，您不需要它。 SQL Fiddle 带有您的测试数据。
非常感谢您一直陪伴着我并回答这个问题，但我仍然遇到了错误 - [1]: ORA-01436: CONNECT BY loop in user data，并且在编辑器中突出显示表名。如果我添加 NOCYCLE 子句，它就会消失...难道数据库被配置为不允许这样吗？ - John Bustos
除了明显的循环 (Parent_ID = ID) 之外，您的实际数据库可能具有多行循环。就像这个两行：10, 20 和 20,10。如果后者确实是这种情况，您将必须使用 NOCYCLE 子句。 - PM 77-1
我看不到如何在不改变数据的情况下避免使用NOCYCLE子句。 - PM 77-1
非常感谢您的帮助和建议 - 我无法正确地运行它，即使使用了NOCYCLE子句...最终@Emmanuel的解决方案完全符合我的需求... - John Bustos
1
一个 CONNECT BY 可以给你直接的父节点，但要获取最终的父节点，我会使用 递归子查询。（Emmanuel 解释的 CONNECT_BY_ROOT 也可以工作）
WITH r (id, parent, ultimate_parent, name, ultimate_parent_name, lvl) as
   (SELECT id, parent_id AS parent, parent_id AS ultimate_parent, name, name as ultimate_parent_name, 0 lvl
    FROM mytable
       WHERE parent_id = id -- identifies a root
UNION ALL
    SELECT m.id, r.id, ultimate_parent, m.name, r.ultimate_parent_name, r.lvl + 1
    FROM r join mytable m on m.parent_id = r.id  -- joins child with parent
    WHERE m.parent_id <> m.id -- to avoid cycles
   )
SELECT * FROM r ;

子查询的第一部分获取根节点，第二部分连接子节点。 Parent 是直接父节点，ultimate_parent 是终极父节点。
“CONNECT BY” 可以用于任何层次结构。在 Oracle 11.2 实现递归因子子查询之前，它是 Oracle 中使用的方法。 - PM 77-1
我正在尝试这个查询语句，但是出现了错误 [1]: ORA-00933: SQL命令未正确结束，并且高亮显示了 SELECT m.id 部分... 这对你有意义吗?? - John Bustos
感谢您的帮助，但@Emmanuel的解决方案最终证明是我想要的准确的。谢谢!!! - John Bustos
0
创建一个名为test_data的表格(order_number number(10), line_id number(10), parent_line_id number (10))。
              insert into test_data values (1000, 101, 100);

              insert into test_data values (1000, 100, '');

              insert into test_data values (3000, 301, 300);

              insert into test_data values (3000, 300, '');


              select * from test_data

              select * from test_data 
              where order_number in (1000,3000) 
              start with parent_line_id is null 
              connect by prior line_id= parent_line_id
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接