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

Question

此SQLFiddle example描述2个表及其关系：


主要路线：两个地方之间的直接路线。间接主要路由用于与辅助路由表建立关系
次要路线：两个地方之间没有直接主要路线的路线


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


伦敦->哈洛：


存在直接路由。 SQL很简单：

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

路由仅在数据库中输入一次，但是路由是双向的。


Stanmore-> Waltham：


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

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

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


伦敦狂欢节：
从上面的1和2没有合适的路线。但是，伦敦-> Harlow和Harlow-Sheering之间存在路线。
温布利-申利：
从1、2或3个拟合中找不到路线。但是，在温布利->伦敦->沃特福德->申利之间，或温布利->伦敦->哈洛->申利之间存在路由


是否有可能构建一个（不是很复杂的）SQL语句来返回3和4的路线，此外，对于找到的每个路线（包括2），必须计算2个点之间的距离，并将其作为路线。

Answer 1

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

预计算那些路线和距离，然后将其填充到第三张表中，可能会更好。例如起点，终点，TransferPoint，ToTransfer_Primary_id，FromTransfer_PrimaryID2，距离。

您将必须分阶段进行构建。

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

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

然后，您将必须编写查询以测试辅助路由之一的传输点。