sql - 查询以获取分层数据及其级别编号

Question

我在 Oracle 10G 数据库中有一个包含以下字段的表

GOAL_ID
PARENT_GOAL_ID
GOAL_NAME

和类似的数据

GOAL_ID PARENT_GOAL_ID   GOAL_NAME
1          null           GoalX
2            1            GoalY
3            1            GoalZ
4            3            GoalN

我需要一个查询，它将通过将级别添加到 GOAL_NAME 来给出结果，如下所示

1     GoalX
1.1   GoalY
1.2   GoalZ
1.2.1 GoalN

子节点的数量可以去任何扩展。所以查询应该可以得到它的层数

Answer 1

您可以使用 recursive subquery factoring (来自 11gR2)通过层次结构工作并构建“级别”标签/字符串，使用分析查询在每次循环中找出当前级别中的位置:

with rcte (root_id, label, goal_level, goal_id, goal_name) as (
  select goal_id,
    to_char(row_number() over (order by goal_id)),
    1,
    goal_id,
    goal_name
  from goals
  where parent_goal_id is null
  union all
  select r.root_id,
    r.label ||'.'|| row_number()
      over (partition by r.root_id, r.goal_level order by g.goal_id),
    r.goal_level + 1,
    g.goal_id,
    g.goal_name
  from rcte r
  join goals g
  on g.parent_goal_id = r.goal_id
)
select label, goal_name
from rcte
order by root_id, goal_level, goal_id;

anchor 成员获取根 ID(那些没有父 ID 的 ID)并将它们分配为层次结构中的级别 1，同时通过分析函数对它们进行顺序编号。

然后递归成员找到子级，增加级别，并将父级子级中的序号连接到标签字符串。

递归继续，直到没有更多的 child 。

在普通 CTE 中使用示例数据和一些额外内容进行演示:

with goals (GOAL_ID, PARENT_GOAL_ID, GOAL_NAME) as (
  select 1, null, 'GoalX' from dual
  union all select 2, 1, 'GoalY' from dual
  union all select 3, 1, 'GoalZ' from dual
  union all select 4, 3, 'GoalN' from dual
  union all select 5, null, 'GoalA' from dual
  union all select 6, 5, 'GoalB' from dual
  union all select 7, 6, 'GoalC' from dual
  union all select 8, 6, 'GoalD' from dual
)
, rcte (root_id, label, goal_level, goal_id, goal_name) as (
  select goal_id,
    to_char(row_number() over (order by goal_id)),
    1,
    goal_id,
    goal_name
  from goals
  where parent_goal_id is null
  union all
  select r.root_id,
    r.label ||'.'|| row_number()
      over (partition by r.root_id, r.goal_level order by g.goal_id),
    r.goal_level + 1,
    g.goal_id,
    g.goal_name
  from rcte r
  join goals g
  on g.parent_goal_id = r.goal_id
)
select label, goal_name
from rcte
order by root_id, goal_level, goal_id;

得到:

LABEL   GOAL_NAME
------- ---------
1       GoalX
1.1     GoalY
1.2     GoalZ
1.2.1   GoalN
2       GoalA
2.1     GoalB
2.1.1   GoalC
2.1.2   GoalD