时间戳

首页 > 解决方案 > 如何在一张桌子内找到中转航班？

sql - 如何在一张桌子内找到中转航班？

问题描述

我正在练习 SQL，我有一个名为的表，flights其中包含航班id、、和行程。我希望能够找到可以在两站或更少站内完成的所有最便宜的航班，同时还显示每个航班有多少站以及航班的总成本。另外，如果两次旅行的费用相同，那么我想要停留最少的一次。origindestinationcost

这是关于 db-fiddle 的表格：https ://www.db-fiddle.com/f/mvvE24KAxnayR9fRmHChMw/1

这是我到目前为止所拥有的，但由于我错过了停靠点和一些航班的数量，它还没有完成。我不确定如何计算航班之间的停靠次数或如何获取所有正在连接的航班。

我觉得这可以在没有递归 CTE 的情况下完成，但我不确定。我也觉得我的查询很乱。任何帮助都会很有用！

谢谢！

询问：

WITH RECURSIVE connecting_flights AS (

  SELECT origin, destination, cost 
  FROM flights

  UNION ALL

  SELECT f.origin, f.destination, cf.cost
  FROM flights f
  INNER JOIN connecting_flights cf ON cf.origin = f.destination

), flight_data as (
    SELECT 
        DISTINCT flights.origin as original_flight
       , flights.destination as original_destination
       , flights.cost as flights_cost
       , cf.origin as cf_origin
       , cf.destination as cf_destination
       ,cf.cost as cf_cost
       , flights.cost + cf.cost as total_cost
    FROM flights
    LEFT JOIN connecting_flights cf ON cf.origin = flights.destination
    LEFT JOIN flights b ON b.origin = cf.origin
) 

SELECT
    original_flight
    , CASE 
        WHEN cf_destination IS NULL THEN original_destination
        ELSE cf_destination
     END as destination
   , CASE
       WHEN cf_destination IS NULL THEN flights_cost
       ELSE total_cost
    END as total
FROM flight_data
ORDER BY original_flight

标签： sqldatabasepostgresqlrecursioncommon-table-expression

解决方案

由于您最多只考虑两个步骤，因此您可以使用union all：

select f.origin, f.destination, 0 steps, f.cost, f.origin || '->' || f.destination route
from flights f
union all
select f1.origin, f2.destination, 1, f1.cost + f2.cost, f1.origin || '->' || f1.destination || '->' || f2.destination route
from flights f1
inner join flights f2 on f1.destination = f2.origin
union all
select f1.origin, f3.destination, 2, f1.cost + f2.cost + f3.cost, f1.origin || '->' || f1.destination || '->' || f2.destination || '->' || f3.destination route
from flights f1
inner join flights f2 on f1.destination = f2.origin
inner join flights f3 on f2.destination = f3.origin
order by cost, steps

结果：

| origin | destination | steps | cost | route              |
| ------ | ----------- | ----- | ---- | ------------------ |
| DFW    | MCO         | 0     | 100  | DFW->MCO           |
| SFO    | DFW         | 0     | 200  | SFO->DFW           |
| DFW    | JFK         | 0     | 200  | DFW->JFK           |
| SFO    | MCO         | 1     | 300  | SFO->DFW->MCO      |
| SFO    | MCO         | 0     | 400  | SFO->MCO           |
| SFO    | JFK         | 1     | 400  | SFO->DFW->JFK      |
| SFO    | JFK         | 0     | 500  | SFO->JFK           |
| JFK    | LHR         | 0     | 1000 | JFK->LHR           |
| DFW    | LHR         | 1     | 1200 | DFW->JFK->LHR      |
| SFO    | LHR         | 2     | 1400 | SFO->DFW->JFK->LHR |
| SFO    | LHR         | 1     | 1500 | SFO->JFK->LHR      |

推荐阅读