我有一张这样的桌子
---------------------------
| 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/