具有最小和最大记录的db2递归

Question

我们需要这个表：

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

含义如果我有：Gid1父亲是Gid2Gid2父亲是Gid3

那么输出将是

Gid1与万物之父Gid3Gid2与父亲的所有是Gid3

我开始写这个查询，但我被卡住了

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

谢谢-

Answer 1

试试这个：

WITH T (OLD_GID, NEW_GID) AS
(
SELECT *
FROM RELATIONS P
WHERE NOT EXISTS (SELECT 1 FROM RELATIONS C WHERE C.OLD_GID = P.NEW_GID)
UNION ALL
SELECT R.OLD_GID, T.NEW_GID
FROM RELATIONS R, T
WHERE R.NEW_GID = T.OLD_GID
)
SELECT * 
FROM T
ORDER BY NEW_GID, OLD_GID

结果是：

OLD_GID	NEW_GID
GID2	GID1
GID3	GID1
GID4	GID1
GID5	GID1
GID10	GID11
GID20	GID21
GID30	GID32
GID31	GID32
GID40	GID43
GID41	GID43
GID42	GID43