In hierarchical data, how to select all nodes up to the root level?

If I have hierarchical data represented in a table like this:

team_id    team_name     parent_id
1          Sales         (null)
2          Executives    (null)
3          EMEA Sales    1
4          APAC Sales    1
5          UK Sales      3

Given one team_id, what would be the SQL to select all team_ids that lead to the root level? I'm using Oracle 11.2.

For example, given team_id 5, the team_ids returned should be: (5,3,1)
Or, given team_id 4, then the output should be: (4,1)

It's reasonably trivial to traverse down tree branches, showing the data hierarchy, using something like:

    SELECT team_id, team_name, parent_id
      FROM teams
START WITH team_name = 'Sales'
CONNECT BY PRIOR team_id = parent_id
;

I am looking for the opposite - traversing up a tree branch from a particular node.

Note that the sample data here is only 3 levels, but I'm looking for a solution for an arbitrary number of levels.

---

WITH test AS
  ( SELECT 1 team_id, 'Sales team_name', NULL parent_id FROM dual
  UNION
  SELECT 2, 'Executives' , NULL FROM dual
  UNION
  SELECT 3, 'EMEA Sales', 1 FROM dual
  UNION
  SELECT 4, 'APAC Sales', 1 FROM dual
  UNION
  SELECT 5, 'UK Sales' , 3 FROM dual
  )
SELECT team_id
FROM test
  START WITH team_id         = 5
  CONNECT BY prior parent_id = team_id

链接地址: http://www.djcxy.com/p/93924.html

上一篇: 如何将xml中的分层数据插入到oracle表中

下一篇: 在分层数据中，如何选择所有节点直到根级？