如何在Postgres/SQLite中递归查询指定城市可到达的所有城镇

PostgreSQL和SQLite都支持WITH RECURSIVE递归公共表表达式语法，可以直接用来实现多跳可达城市查询，不需要额外自定义函数。

注意事项 #

From和To属于SQL保留关键字，查询时必须用双引号包裹转义，否则会触发语法错误。

完整查询代码 #

WITH RECURSIVE reachable_cities AS (
    -- 初始步：获取出发城市的直达目的地
    SELECT "To" AS city
    FROM Train
    WHERE "From" = 'St Louis' -- 此处替换为实际传入的出发城市参数
    
    UNION
    
    -- 递归步：将已查到的可达城市作为新出发地，查询下一级目的地
    SELECT t."To" AS city
    FROM Train t
    INNER JOIN reachable_cities rc ON t."From" = rc.city
    WHERE t."To" NOT IN (SELECT city FROM reachable_cities) -- 避免环线导致的无限递归和重复数据
)
-- 输出所有可达城市
SELECT city FROM reachable_cities;

效果说明 #

用你提供的示例数据集，传入St Louis作为出发城市执行上述查询，返回结果正好是Jefferson City、Kirkwood、Belleville，和预期完全一致。如果存在环线（比如A到B、B到A的路线），查询也会自动去重不会陷入死循环。

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