我有一张这样的桌子:
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}
。我想连接一个数组中来自回溯的所有点。
结果如下:
{1,1},{2,1}{3,1},{3,2},{3,3}
我该怎么办?
最佳答案
这是一个相当简单的递归查询。使用另一列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)
关于sql - 回溯-PostgreSQL,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/47438738/