我在postgres中的表如下所示,表存储了ID之间的一种链式关系,我想要一个可以产生结果的
查询,如“vc1” – > “rc7”或“vc3” – >“rc7”,我只会
查询第一列ID1中的ID
ID1 ID2
"vc1" "vc2"
"vc2" "vc3"
"vc3" "vc4"
"vc4" "rc7"
所以我想在这里提供一些“头”ID,我必须获取尾部(链中的最后一个)id.
这是使用递归CTE的
sql：
with recursive tr(id1,id2,level) as (
select t.id1,t.id2,1 as level
from t union all
select t.id1,tr.id2,tr.level + 1
from t join
tr
on t.id2 = tr.id1
)
select *
from (select tr.*,max(level) over (partition by id1) as maxlevel
from tr
) tr
where level = maxlevel;
Here是sqlFiddle
