我有一张这样的桌子

---------------------------
| id1 | id2 | col1 | col2 |
+-----+-----+------+------+
|  1  |  1  |  a   |  b   |
|-----+-----+------+------|
|  1  |  2  |  b   |  c   |
|-----+-----+------+------|
|  5  |  1  |  d   |  f   |
---------------------------

其思想是表存储路径:a->b->c和d->f。我想要的是一个返回a->c和d->f的查询。

最佳答案

您需要递归查询:

with recursive find_path (col1, col2, depth) as (
    select col1, col2, 1
    from my_table t
    where not exists (
        select 1
        from my_table
        where col2 = t.col1)
union all
    select p.col1, t.col2, depth+ 1
    from my_table t
    join find_path p on p.col2 = t.col1
)

select distinct on (col1) format('%s -> %s', col1, col2) as path
from find_path
order by col1, depth desc;

  path
--------
 a -> c
 d -> f
(2 rows)

这个问题不太清楚。如果您的目标是按id1按id2的顺序获取分区中的路径,则可以使用窗口函数:
select distinct on (id1)
    id1, first_value(col1) over w, last_value(col2) over w
from my_table
window
    w as (partition by id1 order by id2)
order by id1, id2 desc;

 id1 | first_value | last_value
-----+-------------+------------
   1 | a           | c
   5 | d           | f
(2 rows)

关于database - 如何以分层结构返回路径的起点和终点,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/44357362/

10-12 00:29
查看更多