sql - 回溯-PostgreSQL

Question

我有一个看起来像这样的表:

 here  | there | 
-------+-------+
 {1,1} | {1,1} |
 {1,1} | {2,1} |
 {1,1} | {1,2} |
 {1,2} | {1,3} |         
 {2,1} | {2,2} |         
 {2,1} | {3,1} |         
 {3,1} | {3,2} |         
 {2,2} | {2,3} |          
 {3,2} | {3,3} |          

我想做一个从 {3,3} 到 {1,1} 的回溯。我想将 Backtrace 中的所有点连接到一个数组中。

结果是这样的:

{1,1},{2,1}{3,1},{3,2},{3,3}

我该如何管理？

Answer 1

这是一个相当简单的递归查询。使用附加列 depth 获取最终聚合中点的预期顺序。

with recursive backtrace(here, there, depth) as (
    select here, there, 0
    from my_table
    where there = '{3,3}'
union all
    select t.here, t.there, b.depth+ 1
    from my_table t
    join backtrace b on b.here = t.there and b.here <> b.there
)

select string_agg(there::text, ',' order by depth desc) as backtrace
from backtrace

           backtrace           
-------------------------------
 {1,1},{2,1},{3,1},{3,2},{3,3}
(1 row)