从sql中的点数据库获取路径 [英] Get the paths from a database of points in sql

问题描述

考虑我有一个点表，每个点有2个坐标。例如：

Consider that I have a table of points, each point has 2 coordinates. For example:

Source       |       Destination 
1            |       2
2            |       3
3            |       7
5            |       7
9            |       12

我想用SQL编写查询，使我得到以下内容：

I'd like to write a query in SQL that gives me the following:

1. 这些点产生的所有路径。每个路径必须具有连接的点，因为下一个点的源坐标与上一个点的目标点相同。


2. 路径不能是循环的。

例如，在上表中，运行查询应返回3 *路径：

For example, in the table above, running the query should return 3*paths:

1. （1,2）（2,3）（3,7）


2. （5,7）


3. （9， 12）

我考虑过要复制积分表，我们称它们为A和B，然后设置一个条件：

I thought about duplicating the points table, let's call them A and B, then set a condition:

SELECT source, destination 
FROM A, B
WHERE A.source = B.destination

但是我不确定答案，我几乎可以肯定这不是最佳选择。

But I'm not sure about the answer and I'm almost sure it's not optimal.

推荐答案

将递归cte与 array [array [source [目的，目标]] 用作聚合列：

Use a recursive cte with an array[array[source, destination]] as an aggregation column:

with recursive cte(source, destination, path) as (
    select source, destination, array[array[source, destination]]
    from points
union all
    select p.source, p.destination, path || array[p.source, p.destination]
    from cte c
    join points p on c.destination = p.source
    where not array[array[p.source, p.destination]] <@ path
)
select distinct on (path[1:1]) path
from (
    select distinct on (source, destination) *
    from cte
    order by source, destination, array_length(path, 1) desc
    ) s    
order by path[1:1], array_length(path, 1) desc;

        path         
---------------------
 {{1,2},{2,3},{3,7}}
 {{5,7}}
 {{9,12}}
(3 rows)

这篇关于从sql中的点数据库获取路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！