postgresql - 我对 PostgreSQL 有点陌生，需要如何构建复杂的查询

Question

我需要列出在恰好在另一个城市停留后可以到达的所有城市，从我选择的任何城市出发。并列出到最终城市和中间城市的距离。

数据库中的表由城市组成，具有以下属性:

| city_id |   name    |  
  1         Edinburgh  
  2         Newcastle  
  3         Manchester

城市对:

| citypair_id | city_id |  
  1             1  
  1             2  
  2             1    
  2             3  
  3             2  
  3             3

和距离:

| citypair_id | distance |
  1             1234
  2             1324
  3             1324

和训练:

| train_id | departure_city_id | destination_city_id |
  1          1                   2
  2          2                   3
  3          1                   3
  4          3                   2

我没有输入任何数据，但基本上如果我随机选择一个 city.name，我需要找出如果我经过另一个城市(即两个城市)可以从这个城市到达哪些城市旅程)，然后是到最终和中间城市的距离。

您或我应该如何构建查询以返回所需的表？

---

编辑以包含数据和缺失的表格!例如，您可以从爱丁堡 (1) 经纽卡斯尔 (2) 前往曼彻斯特 (3)，也可以从爱丁堡经曼彻斯特前往纽卡斯尔，但是您不能从曼彻斯特经纽卡斯尔前往爱丁堡(因为火车从 3 出发, 到达 2，但没有来自 2 的火车到达 1) 并且这条路线不应从查询中返回。事先对任何混淆表示歉意。

Answer 1

我有一个 CTE 可以构建所有目的地的树。

WITH RECURSIVE trip AS (
  SELECT c.city_id AS start_city,
    ARRAY[c.city_id] AS route,
    cast(c.name AS varchar(100)) AS route_text,
    c.city_id AS leg_start_city,
    c.city_id AS leg_end_city,
    0 AS trip_count,
    0 AS leg_length,
    0 AS total_length
  FROM cities c
UNION ALL
  SELECT
    trip.start_city,
    trip.route || t.destination_city_id,
    cast(trip.route_text || ',' || c.name AS varchar(100)),
    t.departure_city_id,
    t.destination_city_id,
    trip.trip_count + 1,
    d.distance,
    trip.total_length + d.distance
  FROM trains t
  INNER JOIN trip
    ON t.departure_city_id =  trip.leg_end_city
  INNER JOIN citypairs cps
    ON t.departure_city_id = cps.city_id
  INNER JOIN citypairs cpe
    ON t.destination_city_id = cpe.city_id AND
       cpe.citypair_id = cps.citypair_id
  INNER JOIN distances d
    ON cps.citypair_id = d.citypair_id
  INNER JOIN cities c
     ON t.destination_city_id = c.city_id
  WHERE NOT (array[t.destination_city_id] <@ trip.route))
SELECT *
FROM trip
WHERE trip_count = 2
AND start_city = (SELECT city_id FROM cities WHERE name = 'Edinburgh');

CTE从每个城市开始(在开始的非递归部分)，然后确定它可以到达的所有目的地城市。它在一个数组(路线列)中跟踪它去过的所有城市，因此它不会再次循环回到自己。随着它的进展，它会跟踪总行程距离和乘坐的火车数量(在 trip_count 中)。

当它穿过树时，它会保持总距离。

这给出了结果

| START_CITY | ROUTE |                     ROUTE_TEXT | LEG_START_CITY | LEG_END_CITY | TRIP_COUNT | LEG_LENGTH | TOTAL_LENGTH |
--------------------------------------------------------------------------------------------------------------------------------
|          1 | 1,2,3 | Edinburgh,Newcastle,Manchester |              2 |            3 |          2 |       1324 |         2558 |
|          1 | 1,3,2 | Edinburgh,Manchester,Newcastle |              3 |            2 |          2 |       1324 |         2648 |

如果您更改删除最后的 WHERE 子句，它将显示数据中所有可能的行程，同样您可以更改 trip_count 以查找所有单个火车目的地等。

| START_CITY | ROUTE |                     ROUTE_TEXT | LEG_START_CITY | LEG_END_CITY | TRIP_COUNT | LEG_LENGTH | TOTAL_LENGTH |
--------------------------------------------------------------------------------------------------------------------------------
|          1 |     1 |                      Edinburgh |              1 |            1 |          0 |          0 |            0 |
|          2 |     2 |                      Newcastle |              2 |            2 |          0 |          0 |            0 |
|          3 |     3 |                     Manchester |              3 |            3 |          0 |          0 |            0 |
|          1 |   1,2 |            Edinburgh,Newcastle |              1 |            2 |          1 |       1234 |         1234 |
|          1 |   1,3 |           Edinburgh,Manchester |              1 |            3 |          1 |       1324 |         1324 |
|          2 |   2,3 |           Newcastle,Manchester |              2 |            3 |          1 |       1324 |         1324 |
|          3 |   3,2 |           Manchester,Newcastle |              3 |            2 |          1 |       1324 |         1324 |
|          1 | 1,2,3 | Edinburgh,Newcastle,Manchester |              2 |            3 |          2 |       1324 |         2558 |
|          1 | 1,3,2 | Edinburgh,Manchester,Newcastle |              3 |            2 |          2 |       1324 |         2648 |

cast( ... as varchar(100)) 有点老套，我不确定为什么需要它，但我还没有机会解决这个问题。

测试用的SQL:http://sqlfiddle.com/#!1/93964/24