postgresql 如何对递归查询进行求和

ntjbwcob 于 2022-11-23 发布在 PostgreSQL

关注(0)|答案(1)|浏览(224)

我有一个查询，表flights也包含price列。我想把它加起来并显示出来。我该怎么解决这个问题呢？我可以通过某种方式从SELECT * from get_cities;中取值来解决这个问题吗？还是应该在查询中完成？
Table img
我正在努力解决这个问题
编写一个查询，查找飞机经过3个停靠站后可以到达的所有城市 City name 的名称。显示停靠站所在的所有城市以及行程的总费用。同时汇总行程费用。

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
;

postgresql

来源：https://stackoverflow.com/questions/74480209/how-to-add-sum-to-recursive-query

1条答案

按热度按时间

8hhllhi21#

您可以直接在递归cte中对价格求和：

WITH RECURSIVE get_cities AS (
    SELECT 0 as count, array[city] as city_path, array[cid] as cid_path, 0 as total_price 
      FROM cities 
     WHERE CITY = 'Agat'
    UNION ALL
    SELECT c.count + 1, c.city_path || b.city, c.cid_path || b.cid, c.total_price + t.price
      FROM get_cities c
      JOIN flights t on t.departure = c.cid
      JOIN cities b on t.arrival = b.cid
     WHERE COUNT < 3
)
SELECT *
  FROM get_cities
 WHERE count = 2  -- select only the journey with 3 stops ;

赞(0）回复(0）举报 2022-11-23

我来回答

postgresql 如何对递归查询进行求和

1条答案

相关问题

热门标签

最新问答