如何查询(几乎)树结构

Hi I got this table:

嗨，我有这张桌子:

id   replacement id
1     2
2     1
2     3
3     2
2     4
4     2
10    11
11    10

The id(objects) are in a tree structure but the table is designed poorly for a tree structure. The connection between the nodes goes both ways.

id(对象)在树结构中，但是表在树结构中设计得很差。节点之间的连接是双向的。

The only way to determine if one is the parent of another is this table:

确定一个人是否是另一个人的父亲的唯一方法是这个表格:

id  valid_until
1
2   2014-01-01
3   2013-01-01
4   2013-01-01
10  
11  2014-01-01

2 is parent of 3 and 4 because its valid_until is later than 3 and 4. 1 is top node because it's still valid.

2是3和4的父结点，因为它的valid_until要晚于3和4。1是top节点，因为它仍然有效。

I'm struggling to write a query of this data so it could look like this:

我在努力编写这个数据的查询，所以它可以是这样的:

id  parent
1
2   1
3   2
4   2

Is there a way to make this as a single query or do I've to create a procedure and a temporary view for this?

有没有一种方法可以让它成为一个单一的查询或者我需要创建一个过程和一个临时视图?

EDIT: I added some new rows to my table. So basically I also want to say in the query that I only want id: 1,2,3 or 4's tree not 10 and 11. How do I do that?

编辑:我在表中添加了一些新行。基本上我也想在查询中说我只想要id: 1 2 3或4的树而不是10和11。我该怎么做呢?

2 个解决方案

#1

This answer answers the follow up question of only getting the records that belongs to a particular root node:

此答案回答了以下问题:只获取属于特定根节点的记录:

select id, parent_id, level
from (
select aid id, case when adate = to_date('9999','yyyy') then null else rid end parent_id
from (select
   c.aid,
   c.rid,
   c.adate,
   nvl(b.d,to_date('9999','yyyy')) bdate from (select a.id aid, a.rid rid, nvl(d,to_date('9999','yyyy')) adate from a, b where a.id = b.id) c, b where c.rid = b.id
) where adate < bdate or adate = to_date('9999','yyyy')
) 
 START WITH id =1  -- ID of the ROOT node
connect by prior id = parent_id
order by id

#2

I believe this will work

我相信这行得通

create table a (id number, rid number);

insert into a values(1,2);
insert into a values(2,1);
insert into a values(2,3);
insert into a values(3,2);
insert into a values(2,4);
insert into a values(4,2);

create table b (id number, d date);

insert into b values(1, null);
insert into b values(2, to_date('2014-01-01','yyyy-mm-dd'));
insert into b values(3, to_date('2013-01-01','yyyy-mm-dd'));
insert into b values(4, to_date('2013-01-01','yyyy-mm-dd'));

select aid id, case when adate = to_date('9999','yyyy') then null else rid end parent_id
from (
   select
     c.aid,
     c.rid,
     c.adate,
     nvl(b.d,to_date('9999','yyyy')) bdate 
   from (
         select a.id aid, a.rid rid, nvl(d,to_date('9999','yyyy')) adate 
         from a, b where a.id = b.id
         ) c, b 
          where c.rid = b.id
      ) where adate < bdate or adate = to_date('9999','yyyy')
order by id

#1