sql - DB2：递归查询

Question

需要帮忙。

表：M_Objects

Col:    Id     Child_Id
Row1    M1       M2
Row2    M3       M4
Row3    M2       M5
Row4    M5       M6

我想在我的查询中查询这些结果，行：1,3,4 当我要求 Id M1 时。所以对于Id，它应该寻找child-id并去id ...

WITH RPL (oid, ocid) AS
     (  SELECT Upp.ID, Upp.CHILD_ID
        FROM M_ObjectsUpp
        WHERE Upp.ID = 'M1'
      UNION ALL
       SELECT child.ID, child.CHILD_ID
        FROM RPL ab, M_Objects child
        WHERE CHILD.child_id = ab.oid
     )
SELECT *
FROM RPL
  

标签： sqlrecursiondb2

Answer 1

您正在自上而下地遍历，从 ID 开始，然后使用其 child_id 查找下一个 ID。因此，您必须将递归部分中的 WHERE 更改为：

WITH RPL (oid, ocid) AS
     (  SELECT Upp.ID, Upp.CHILD_ID
        FROM M_Objects Upp
        WHERE Upp.ID = 'M1'
      UNION ALL
       SELECT child.ID, child.CHILD_ID
        FROM RPL ab, M_Objects child
        WHERE CHILD.id = ab.child_id
     )
SELECT *
FROM RPL