oracle 9i 获取具有给定孩子的树的最高成员

Question

我在 Oracle 9i 数据库表中有一个父子关系，

如下所示：

parent | child  
1      | 2  
2      | 3
2      | 4
null   | 1
1      | 8

我需要从给定子项获取绝对父项。 假设我有孩子 4，它必须给我父母： 1

我已经查看过 CONNECT BY ，但我找不到解决方案。

Answer 1

您可以使用 CONNECT BY 查询来构建父母列表，然后进行过滤：

SQL> WITH tree AS (
  2     SELECT 1 parent_id, 2 child_id FROM DUAL
  3     UNION ALL SELECT 2   , 3  FROM DUAL
  4     UNION ALL SELECT 2   , 4  FROM DUAL
  5     UNION ALL SELECT null, 1  FROM DUAL
  6     UNION ALL SELECT 1   , 8  FROM DUAL
  7  )
  8  SELECT child_id
  9    FROM (SELECT *
 10            FROM tree
 11          CONNECT BY PRIOR parent_id = child_id
 12           START WITH child_id = 4)
 13   WHERE parent_id IS NULL;

  CHILD_ID
----------
         1

Answer 2

SELECT  parent
FROM    (
        SELECT  parent
        FROM    (
                SELECT  parent, level AS l
                FROM    mytable
                START WITH
                        child = 4
                CONNECT BY
                        child = PRIOR parent
                )
        ORDER BY
                l DESC
        )
WHERE   rownum = 1

这将为您提供 NULL 作为绝对父级。

如果您想要 1，请将 parent 替换为 child：

SELECT  child
FROM    (
        SELECT  child
        FROM    (
                SELECT  child, level AS l
                FROM    mytable
                START WITH
                        child = 4
                CONNECT BY
                        child = PRIOR parent
                )
        ORDER BY
                l DESC
        )
WHERE   rownum = 1