如何在递归SQL查询中实现行程费用求和？

问题描述 #

我编写了一段递归SQL查询代码，flights表包含price字段，需求是找出从Agat出发、经3次中转可到达的所有城市，显示中转停留的城市及行程总成本，同时汇总行程费用。想知道能否通过SELECT * from get_cities;取值实现求和，还是必须在查询内完成？

现有代码如下：

WITH RECURSIVE get_cities AS (
    SELECT 0 as count, city, cid from cities where CITY = 'Agat'
    UNION ALL
    SELECT c.count + 1, b.city, b.cid from get_cities c
    JOIN flights t on t.departure = c.cid
    JOIN cities b on t.arrival = b.cid
    WHERE COUNT < 3
)

SELECT cid, sum(price) from get_cities
    JOIN flights f on f.fid = cid
    GROUP BY cid
;

解答 #

首先，你当前的递归CTE get_cities仅记录了城市ID、名称和中转次数，没有累积行程费用数据，直接执行SELECT * from get_cities;根本无法获取费用信息，自然没法完成求和，必须在递归查询过程中就同步累积费用。

核心修正思路 #

1. 锚点查询：从Agat出发时，初始化总费用为0，同时记录当前城市、ID、中转次数，可选记录完整行程路径（方便查看中转节点）。
2. 递归累积费用：每次关联flights表时，将当前航班的price累加到之前的总费用中，同时中转次数+1，直到次数达到3次（3次中转对应4段行程：起点→中转1→中转2→中转3→终点）。
3. 最终结果输出：过滤出中转次数为3的记录，既可以展示单行程的详细信息，也能按终点城市汇总总费用。

修正后的代码示例 #

WITH RECURSIVE trip_itineraries AS (
    -- 锚点：从Agat出发，初始费用0，中转次数0，记录行程起点
    SELECT 
        0 AS transfer_count,
        c.city AS current_city,
        c.cid AS current_cid,
        0 AS total_price,
        CAST(c.city AS VARCHAR(255)) AS itinerary_path
    FROM cities c 
    WHERE c.city = 'Agat'
    UNION ALL
    -- 递归：关联航班，累加费用，更新中转次数和行程路径
    SELECT 
        ti.transfer_count + 1,
        b.city,
        b.cid,
        ti.total_price + t.price,
        CONCAT(ti.itinerary_path, ' → ', b.city)
    FROM trip_itineraries ti
    JOIN flights t ON t.departure = ti.current_cid
    JOIN cities b ON t.arrival = b.cid
    WHERE ti.transfer_count < 3 -- 限制中转次数不超过3次
)
-- 1. 展示所有3次中转的完整行程及单行程总成本
SELECT 
    itinerary_path AS 完整行程,
    current_city AS 终点城市,
    total_price AS 行程总成本
FROM trip_itineraries
WHERE transfer_count = 3;

-- 2. 按终点城市汇总所有可行程的总费用
SELECT 
    current_city AS 终点城市,
    SUM(total_price) AS 该城市所有行程总费用
FROM trip_itineraries
WHERE transfer_count = 3
GROUP BY current_city;

关键问题说明 #

• 原代码逻辑错误：递归CTE未携带费用信息，后续关联flights时用f.fid = cid的匹配逻辑不成立（cid是城市ID，fid是航班ID，二者无关联），导致无法获取正确费用数据。
• 必须在递归中累积费用：行程是逐段构建的，每段航班的费用需要和之前的行程累加，只有在递归步骤中记录累加值，才能得到完整行程的总成本。
• 中转次数逻辑：transfer_count=0代表起点，transfer_count=3刚好是完成3次中转后的终点状态，对应完整的4段行程。

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