使用SQL获取记录之间的路径？

Question

这个SQLFiddle实例描述了两个表及其关系：

1. 主要路线:两个地方之间的直接路线。间接主路由用于与辅助路由表的关系。
2. 次要路线:两处之间没有直接主干道的路线。

现在，用户希望从一个地方到另一个地方。因此，对于此示例，用户选择以下要点：

1. 伦敦->哈洛：

有一条直接的路线。SQL很简单：

SELECT * 
FROM primary_routes 
WHERE 
    (
        (point1 = 'London' AND point2 = 'Harlow') 
        OR (point1 = 'Harlow' AND point2 = 'London')
    ) 
    AND direct = 1 

一个路由只在DB中输入一次，但是路由是双向的。

1. 斯坦莫尔->沃尔瑟姆：

不存在直接路线，但这两个点都位于同一条路线上。SQL是：

SELECT DISTINCT primary_id 
FROM secondary_routes 
WHERE point IN ( 'Stanmore', 'Waltham')

现在，复杂性将增加，因为可能存在其他类型的连接，例如：

1. 伦敦-希林:从1到2以上的路线都不适合。然而，伦敦->哈洛和哈洛-希林之间有路线.
2. 温布利-申利:从1，2，或3点出发，没有路线。然而，温布利->伦敦->沃特福德->申利或温布利->伦敦->哈洛->申利之间有航线。

是否可以构建一个(不太复杂) SQL语句来返回3和4的路由，而且，对于找到的每一条路由(包括2)，必须计算两个点之间的距离，并将其作为路由的一部分。

Answer 1

总之，不存在简单的 SQL查询，因为您的数据结构可以很容易地找到这些路由。

你最好预先计算这些路线和距离，然后把它们填充到第三张表中。例如StartPoint，EndPoint，TransferPoint，ToTransfer_Primary_id，FromTransfer_PrimaryID2，。

你得分阶段建立起来。

例如，对于伦敦-> Harlow，您可以使用主要路线

select firstroute.point1 as startpoint, firstroute.id as ToTransfer_Primary_id, firstroute.point2 as transferpoint, secondroute.id as FromTransfer_Primary_id , secondroute.point2 as endpoint
from primary_routes as firstroute
inner join primary_routes as secondroute on secondroute.point1 = firstroute.point2
WHERE firstroute.point1 = 'London'
AND secondroute.point2 = 'Harlow'

这给了你

startpoint  ToTransfer_Primary_id   transferpoint   FromTransfer_Primary_id endpoint
London      2                       Watford         4                       Harlow

然后，您必须编写一个查询来测试其中一个辅助路由上的传输点。

Answer 2

我没有在您发布的链接中看到直接的路由距离(您需要计算总距离)，但是您可以比较表的两个副本中的点，这些副本是您想要的a.point1和b.point2，两者都有一个公共点a.point2=b.point1。

select 
   a.point1 as startpoint,
   b.point2 as endpoint, 
   a.point2 as midpoint,
from primary_routes a
join primary_routes as b on b.point1=a.point2   
where a.point1 like '%London%'
    and
    b.point2 like '%Harlow%'