Postgresql递归自连接 [英] Postgresql recursive self join

问题描述

我的表在postgres看起来像下面，表存储链类别的ID之间的关系，我想有一个查询，可以产生结果像vc1 - >rc7或vc3 - >rc7 ，我将只查询第一列ID1中的ID

My table in postgres looks like below, Table stores a chain sort of relation between IDs and I want to have a query which can produce the result like "vc1" -> "rc7" or "vc3"->"rc7", I will only query on the IDs in first column ID1

ID1     ID2
"vc1"   "vc2"
"vc2"   "vc3"
"vc3"   "vc4"
"vc4"   "rc7"

所以我想提供一些headid这里我必须获取尾部（链中的最后）id。

So I want to supply some "head" id here for which I have to fetch the tail(last in the chain) id.

推荐答案

下面是使用递归CTE的SQL：

Here is the SQL using a recursive CTE:

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;

这里是SQLFiddle

Here is the SQLFiddle

这篇关于Postgresql递归自连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！