如何用递归查询获取数据

Question

我想写一个查询，查找从城市Agat乘火车最多3站可到达的所有城市。我试图解决这个问题，但我一直得到错误，不知道如何写这个更好?

表是这样的

CREATE TABLE CITIES (
  CID int primary key,
  CITY varchar(50) default NULL
);
CREATE TABLE Trains (
  departure integer references Cities(cid),
  arrival integer references Cities(cid),
  railline varchar(255) default NULL,
  Tid integer primary key,
  Price integer default NULL
);

这是递归查询，我得到这个错误

ERROR:  operator does not exist: integer = character varying
LINE 6:   INNER JOIN trains t ON t.arrival = c.city

WITH recursive get_cities AS (
  SELECT 0 AS stops, city FROM cities WHERE city = 'Agat'
  UNION
  SELECT r.stops + 1 AS stops, c.city
  FROM cities c
  INNER JOIN trains t ON t.arrival = c.city
  INNER JOIN get_cities r ON r.city = t.departure AND r.stops < 3
)
select * from get_cities;

Answer 1

注释:

1. 在查询中，您必须引用表city的cid列而不是city列

2. 可选:您可以显式递归查询的参数(参见手册):WITH RECURSIVE get_cities (stops, city) AS ( ... )

3. 可选:停止递归的条件应该在递归项的WHERE子句中:WHERE r.stops < 3

4. 可选:我建议将JOIN子句中从get_cities开始的表的顺序颠倒，以提高可读性

Try this:

WITH RECURSIVE get_cities (stops, cid) AS (
  SELECT 0, cid
    FROM cities 
   WHERE city = 'Agat'
UNION ALL
  SELECT r.stops + 1 AS stops, c.cid
    FROM get_cities r 
   INNER JOIN trains t ON t.departure = r.cid
   INNER JOIN cities c ON c.cid = t.arrival
   WHERE r.stops < 3
)
select * from get_cities;

更新:

两个不同的表Trains &Flights，你必须LEFT JOIN两个表，然后合并结果与COALESCE:

WITH RECURSIVE get_cities (stops, cid) AS (
  SELECT 0, cid
    FROM cities 
   WHERE city = 'Agat'
UNION ALL
  SELECT r.stops + 1 AS stops, c.cid
    FROM get_cities r 
    LEFT JOIN trains t ON t.departure = r.cid
    LEFT JOIN flights f ON f.departure = r.cid
   INNER JOIN cities c ON c.cid = COALESCE(t.arrival, f.arrival)
   WHERE r.stops < 3
)
select * from get_cities;