我们需要从该表中:
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
谢谢--
1条答案
按热度按时间kfgdxczn1#
试试看:
其结果是:
| 旧GID|新建_GID|
| - -|- -|
| GID 2型|GID 1型|
| GID 3型|GID 1型|
| GID 4语言|GID 1型|
| 第五代|GID 1型|
| GID 10型|GID 11基因|
| GID 20型|GID 21基因|
| GID 30型|GID 32基因|
| GID 31基因|GID 32基因|
| GID 40型|GID 43基因|
| GID 41基因|GID 43基因|
| GID 42基因|GID 43基因|