sql - 如何通过行层次结构进行 SQL 查询？

Question

假设我在 Oracle 数据库中具有以下表结构，其中 PARENT 引用同一表中的另一行。

  id  | parent
  1   | null
  2   | 1
  3   | 2
  4   | 3
  5   | null
  6   | 5
  7   | 6

我如何查询，以便每一行都让我知道序列的结尾。那就是我想看到这个最终结果：

  id  | end_parent
  1   | 4
  2   | 4
  3   | 4
  4   | 4
  5   | 7
  6   | 7
  7   | 7

标签： sqloracleoracle11g

Answer 1

以下查询显示了您想要的结果，在 Oracle 12c 上进行了测试：

with
x (id, parent, origin, generation) as (
  select id, parent, id, 1 from my_table where parent is null
  union all
  select t.id, t.parent, x.origin, x.generation + 1
   from my_table t
  join x on t.parent = x.id
),
y (id, parent, generation, origin, rn) as (
  select id, parent, generation, origin, 
    row_number() over(partition by origin order by generation desc) as rn
  from x
),
z (id, origin) as (
  select id, origin from y where rn = 1
)
select x.id, z.id as end_parent
from x
join z on x.origin = z.origin
order by x.id

结果：

ID  END_PARENT
--  ----------
1   4
2   4
3   4
4   4
5   7
6   7
7   7

作为参考，这是我使用的数据：

create table my_table (
  id int,
  parent int
);

insert into my_table (id, parent) values (1, null);
insert into my_table (id, parent) values (2, 1);
insert into my_table (id, parent) values (3, 2);
insert into my_table (id, parent) values (4, 3);
insert into my_table (id, parent) values (5, null);
insert into my_table (id, parent) values (6, 5);
insert into my_table (id, parent) values (7, 6);