使用Postgres的递归/层次查询

钟青乐

表： Flight (flight_num,src_city,dest_city,dep_time,arr_time,airfare,mileage)
我需要找到最便宜的票价，从任何给定的源城市到任何给定的目标城市。问题是，这可能涉及 多个航班 所以，比如我从蒙特利尔->堪萨斯城
出发，我可以从蒙特利尔->从华盛顿出发，然后从华盛顿出发->堪萨斯城等等。我将如何使用它Postgres查询生成此信息？
样本数据：
create table flight(  flight_num BIGSERIAL PRIMARY KEY, source_city varchar, dest_city varchar, dep_time int, arr_time int, airfare int, mileage int);insert into flight VALUES Montreal','NY       0530， 0645，  180， 170)， (102， Montreal','Washington 0100， 0235，  100， 180)， (103，  NY',  'Chicago    0800， 1000，  150， 300)， (105，  Washington','KansasCity',          0600， 0845，  200， 600)Washington','NY       1200， 1330，  50， 80)， (107， Chicago', 'SLC',    1100, 1430, 220,    750), (110, 'KansasCity', 'Denver       1400， 1525，  180， 300)， (111， KansasCity', 'SLC       1300， 1530，  200， 500)， (112，  SLC SanFran       1800，  1930，  85， 210)， (113，  SLC LA       1730，             1900，  185， 230)Denver','SLC       1500， 1600， 75， 300)， (116，  SanFran', 'LA       2200， 2230， 50，    75)， (118， LA',  'Seattle       2000， 2100，  150，    450)；
解决方案:
                                                            [基于戈登的答案]
我将arr_time和dep_time更改为TIME数据类型使计算更容易。还添加了它total_time和waiting_time的结果列。
注意 :如果图中可能有任何循环，则需要避免循环(可能使用数组存储路径)
WITH RECURSIVE segs AS (  SELECT f0.flight_num::text as flight  src_city,dest_city  dep_time AS departure  arr_time AS arrival  airfare,mileage  1 as hops  (arr_time - dep_time)::interval AS total_time  '00:00'::interval as waiting_time  FROM flight f0  WHERE src_city = 'SLC' -- UNION ALL  SELECT s.flight || '-->' || f1.flight_num::text as flight  s.src_city,f1.dest_city  s.departure AS departure  f1.arr_time AS arrival  s.airfare f1.airfare as airfare  s.mileage f1.mileage as mileage  s.hops 1 AS hops  s.total_time (f1.arr_time - f1.dep_time)::interval AS total_time  s.waiting_time (f1.dep_time - s.arrival)::interval AS waiting_time  FROM segs s    JOIN flight f    ON f1.src_city = s.dest_city    AND f1.dep_time > s.arrival -- you can't leave until you are there)SELECT *FROM segsWHERE dest_city = 'LA' -- ORDER BY airfare desc ;仅供参考：表结构的变化：
create table flight  ( flight_num BIGSERIAL PRIMARY KEY  ,src_city varchar  ,dest_city varchar  ,dep_time TIME  ,arr_time TIME  ,airfare INTEGER  ,mileage INTEGER);并向数据：
insert into flight VALUES Montreal  NY          Montreal  Washington       NY',             'Chicago       Washington KansasCity       Washington',                         NY',             12:00', '13:30', 50,    80), (107, 'Chicago',    SLC',             11:00', '14:30', 220,    750), (110, 'KansasCity', Denver',          14:00', '15:25', 180,    300), (111, 'KansasCity', SLC',             13:00', '15:30', 200,    500), (112, 'SLC',          SanFran',       18:00', '19:30',             85， 210)SLC    LA             Denver SLC          SanFran  LA          LA    Seattle          20:00， 21:00，  150， 450)；

使用Postgres的递归/层次查询

钟青乐 LV1