如何编写查询语句查找两点间路径并关联原始表连接信息

你的需求本质是无向图的两点路径遍历，主流关系型数据库（MySQL 8.0+/PostgreSQL/SQL Server等）都可以通过**递归公共表表达式（CTE）**实现，以下是具体代码：

表结构说明 #

示例表名为ridge_connect，字段定义如下：

示例数据：

-- 建表语句
CREATE TABLE ridge_connect (
    RidgeId INT PRIMARY KEY,
    PointId1 INT NOT NULL,
    PointId2 INT NOT NULL
);
-- 插入测试数据
INSERT INTO ridge_connect VALUES
(1,1,2),(2,3,2),(3,17,10),(4,18,10),(6,18,11),(7,11,3),
(8,4,1),(9,13,4),(10,16,13),(11,15,16),(12,5,15),(13,19,5),
(14,20,19),(15,21,20),(16,8,21),(17,12,8),(18,6,12);

基础功能：查询两点连通路径 #

以查询点3到点10的路径为例，代码如下（MySQL 8.0+版本适用）：

WITH RECURSIVE path_search AS (
    -- 锚点层：从起点3出发，匹配所有直接关联的边
    SELECT 
        -- 统一路径方向，保证起点在前
        IF(PointId1=3, PointId1, PointId2) AS from_point,
        IF(PointId1=3, PointId2, PointId1) AS to_point,
        CAST(CONCAT(PointId1, ',', PointId2) AS CHAR(200)) AS visited_points,
        1 AS depth
    FROM ridge_connect
    WHERE PointId1 = 3 OR PointId2 = 3

    UNION ALL

    -- 递归层：沿着当前路径继续遍历邻接点
    SELECT 
        ps.to_point AS from_point,
        IF(rc.PointId1=ps.to_point, rc.PointId2, rc.PointId1) AS to_point,
        CAST(CONCAT(ps.visited_points, ',', IF(rc.PointId1=ps.to_point, rc.PointId2, rc.PointId1)) AS CHAR(200)) AS visited_points,
        ps.depth + 1 AS depth
    FROM path_search ps
    INNER JOIN ridge_connect rc
        -- 匹配和当前终点相连的边
        ON (rc.PointId1 = ps.to_point OR rc.PointId2 = ps.to_point)
        -- 排除已经走过的点，避免循环
        AND FIND_IN_SET(IF(rc.PointId1=ps.to_point, rc.PointId2, rc.PointId1), ps.visited_points) = 0
    WHERE ps.to_point != 10 -- 走到目标点10就停止当前路径遍历
)
-- 取最短路径，如需返回所有可行路径去掉ORDER BY和LIMIT即可
SELECT visited_points AS full_path FROM path_search WHERE to_point = 10 ORDER BY depth ASC LIMIT 1;

运行后返回的完整路径为3,11,18,10，拆分后即可得到你需要的点对列表。

扩展功能：关联原始表连接信息 #

只需要在递归过程中记录每一步对应的RidgeId，最终输出时关联对应行的所有字段即可，代码如下：

WITH RECURSIVE path_search AS (
    SELECT 
        rc.RidgeId,
        IF(PointId1=3, PointId1, PointId2) AS from_point,
        IF(PointId1=3, PointId2, PointId1) AS to_point,
        CAST(CONCAT(PointId1, ',', PointId2) AS CHAR(200)) AS visited_points,
        1 AS depth
    FROM ridge_connect rc
    WHERE PointId1 = 3 OR PointId2 = 3

    UNION ALL

    SELECT 
        rc.RidgeId,
        ps.to_point AS from_point,
        IF(rc.PointId1=ps.to_point, rc.PointId2, rc.PointId1) AS to_point,
        CAST(CONCAT(ps.visited_points, ',', IF(rc.PointId1=ps.to_point, rc.PointId2, rc.PointId1)) AS CHAR(200)) AS visited_points,
        ps.depth + 1 AS depth
    FROM path_search ps
    INNER JOIN ridge_connect rc
        ON (rc.PointId1 = ps.to_point OR rc.PointId2 = ps.to_point)
        AND FIND_IN_SET(IF(rc.PointId1=ps.to_point, rc.PointId2, rc.PointId1), ps.visited_points) = 0
    WHERE ps.to_point != 10
)
-- 输出每一步的完整连接信息
SELECT r.RidgeId, r.PointId1, r.PointId2, ps.from_point, ps.to_point
FROM path_search ps
INNER JOIN ridge_connect r ON ps.RidgeId = r.RidgeId
-- 筛选最短路径包含的所有步骤
WHERE ps.visited_points LIKE CONCAT('%', (SELECT visited_points FROM path_search WHERE to_point = 10 ORDER BY depth ASC LIMIT 1), '%')
ORDER BY ps.depth ASC;

运行后返回结果如下，符合你要求的关联原始表信息的效果：

其他说明 #

• PostgreSQL等其他数据库语法差异很小，仅需要调整字符串拼接、类型转换、判断点是否存在的函数即可，逻辑完全通用。
• 如果路径深度超过数据库默认递归限制，可手动调整递归深度参数适配。

内容的提问来源于stack exchange，提问作者NewHorse