我们需要从该表中：

drop table relations; 
create table relations (OLD_GID varchar(60), NEW_GID varchar(60)); 
insert into relations values('GID5','GID4'); 
insert into relations values('GID4','GID3'); 
insert into relations values('GID2','GID1'); 
insert into relations values('GID3','GID2'); 
insert into relations values('GID10','GID11'); 
insert into relations values('GID20','GID21'); 
insert into relations values('GID30','GID32'); 
insert into relations values('GID31','GID32'); 
insert into relations values('GID40','GID42'); 
insert into relations values('GID41','GID42'); insert into relations values('GID42','GID43');

以这样的表结束

OLD_GID                                             NEW_GID           

------------------------------------------------------------
GID5                                                GID1  
GID4                                                GID1  
GID3                                                GID1 
GID2                                                GID1

GID40                                               GID43 
GID41                                               GID43 
GID42                                               GID43

GID10                                               GID11

GID20                                               GID21

GID30                                               GID32 
GID31                                               GID32

意思是如果我有：Gid 1的父亲是Gid 2，Gid 2的父亲是Gid 3
则输出将是
Gid 1和所有Gid 3的父亲Gid 2和所有Gid 3的父亲
我开始写这个查询但是我卡住了

WITH RPL (OLD_GID, NEW_GID) AS
     (  SELECT ROOT.OLD_GID, ROOT.NEW_GID
        FROM relations ROOT
        WHERE ROOT.OLD_GID in ( select OLD_GID from relations where OLD_GID  not in ( select NEW_GID from relations ))
      UNION ALL
        SELECT CHILD.OLD_GID, CHILD.NEW_GID
        FROM RPL PARENT, relations CHILD
        WHERE  PARENT.NEW_GID = CHILD.OLD_GID
     )
select --  row_number() over(order by 1) as genkeycol  , OLD_GID, NEW_GID
        OLD_GID, NEW_GID
from RPL

谢谢--