oracle - 在 Oracle SQL 中查找层次关系中每个项目的顶级

Question

我正在尝试查询代表某些分层数据的(大型)Oracle 9 表。 Parent 项目有自己的 Id 作为 Parent ID。举个例子；

ID    PARENTID               
----- --------  
1     1
2     1
3     2
4     2
5     3
6     6
7     6
8     6
9     4
10    10

我想要一个返回每个 ID 的查询，以及该 ID 的最终父级，所以继续我的例子

ID    UlitimateParent
----  ----
1     1
2     1
3     1
4     1
5     1
6     6
7     6
8     6
9     1
10    10

我看过几个使用 Connect By 的例子，但似乎无法让它发挥作用。有什么想法吗？

Answer 1

在 10g+ 中，您将使用 CONNECT_BY_ROOT功能:

SQL> with data as (
  2     SELECT 1 id, 1 parent_id FROM DUAL
  3     UNION ALL SELECT 2 , 1  FROM DUAL
  4     UNION ALL SELECT 3 , 2  FROM DUAL
  5     UNION ALL SELECT 4 , 2  FROM DUAL
  6     UNION ALL SELECT 5 , 3  FROM DUAL
  7     UNION ALL SELECT 6 , 6  FROM DUAL
  8     UNION ALL SELECT 7 , 6  FROM DUAL
  9     UNION ALL SELECT 8 , 6  FROM DUAL
 10     UNION ALL SELECT 9 , 4  FROM DUAL
 11     UNION ALL SELECT 10, 10 FROM DUAL
 12  )
 13  SELECT id, connect_by_root(id) ultimate_parent_id
 14    FROM data
 15  START WITH id = parent_id
 16  CONNECT BY parent_id = PRIOR id AND id != PRIOR id  ;

ID  ULTIMATE_PARENT_ID
--- ------------------
  1                  1
  2                  1
  3                  1
  5                  1
  4                  1
  9                  1
  6                  6
  7                  6
  8                  6
 10                 10

在 9i 中，您可以使用 SYS_CONNECT_BY_PATH (带有适当的子字符串):

SQL> with data as (
  2     SELECT 1 id, 1 parent_id FROM DUAL
  3     UNION ALL SELECT 2 , 1  FROM DUAL
  4     UNION ALL SELECT 3 , 2  FROM DUAL
  5     UNION ALL SELECT 4 , 2  FROM DUAL
  6     UNION ALL SELECT 5 , 3  FROM DUAL
  7     UNION ALL SELECT 6 , 6  FROM DUAL
  8     UNION ALL SELECT 7 , 6  FROM DUAL
  9     UNION ALL SELECT 8 , 6  FROM DUAL
 10     UNION ALL SELECT 9 , 4  FROM DUAL
 11     UNION ALL SELECT 10, 10 FROM DUAL
 12  )
 13  SELECT id, sys_connect_by_path(id, '->') path
 14    FROM data
 15  START WITH id = parent_id
 16  CONNECT BY parent_id = PRIOR id
 17         AND id != PRIOR id;

        ID PATH
---------- --------------------
         1 ->1
         2 ->1->2
         3 ->1->2->3
         5 ->1->2->3->5
         4 ->1->2->4
         9 ->1->2->4->9
         6 ->6
         7 ->6->7
         8 ->6->8
        10 ->10