递归查询

Question

我想得到关于我想做的递归查询的帮助。 我在 DB2 中创建了这个表：

connect reset;
connect to sample;
DROP TABLE FLIGHTS;

CREATE TABLE FLIGHTS
 (START         VARCHAR(16)     NOT NULL,
  DESTINATION   VARCHAR(16)     NOT NULL,
  DISTANCE  BIGINT          NOT NULL    
 );
insert into FLIGHTS values ('Dublin','Bhogrol',5340);
insert into FLIGHTS values ('Dublin','Smallville',5500);
insert into FLIGHTS values ('Smallville','Seattle',1300);
insert into FLIGHTS values ('Smallville','Clacton',6700);
insert into FLIGHTS values ('Bhogrol','Moscow',2320);
insert into FLIGHTS values ('Moscow','Seattle',3600);
insert into FLIGHTS values ('Bhogrol','Smallville',2950);
insert into FLIGHTS values ('Rome','Bhogrol',720);
insert into FLIGHTS values ('Clacton','Moscow',6700);
insert into FLIGHTS values ('Rome','Smallville',3050);
insert into FLIGHTS values ('Schippol','Smallville',8990);
insert into FLIGHTS values ('Seattle','Schippol',7840);
insert into FLIGHTS values ('Bhogrol','Clacton',1300);
insert into FLIGHTS values ('Bilbao','Moscow',1270);
insert into FLIGHTS values ('Smallville','Schippol',8990);

我想查找从都柏林出发且少于 8 次的所有转机航班 停止。查询的输出应该是具有以下 3 个字段的一组行 格式：[路线，距离，停靠点]其中路线字段是由 从都柏林出发的每条连接路径所访问的机场。这是机场名称的序列 用“-”字符分隔。但不要显示整个机场名称，只需显示第一个 三个字符，使每个字符串看起来像 例如，Dub＞Sch＞Dub＞Sma＞Sch＞Dub＞Sma＞Cla＞Mos。 属性“route”的类型应为 VARCHAR(40)。

我想这样开始：

WITH path (start, destination, distance, stops)
AS(
  SELECT f.start, f.destination, f.distance, 0
  FROM flights f
  WHERE start = 'Dublin'
  UNION ALL
  SELECT p.start, f.destination,
  p.distance + f.distance, p.stops+1
  FROM flights f, path p
  WHERE p.destination = f.start AND p.stops < 8 
)
SELECT start, destination, distance, stops 
FROM path;

这是正确的吗？如果是，那我该怎么办？

Answer 1

我想你正在寻找这样的东西：

WITH path (route, start, destination, distance, stops)
AS
(
  SELECT substr(f.start,1,3) || ' > '|| substr(f.destination,1,3) as route,
         f.start, 
         f.destination,
         f.distance, 
         0
  FROM flights f
  WHERE start = 'Dublin'
  UNION ALL
  SELECT p.route || ' > ' || substr(f.destination,1,3) as route,
         f.start,
         f.destination,
         p.distance + f.distance, 
         p.stops + 1
  FROM flights f
    JOIN path p ON p.destination = f.start AND p.stops < 8 
)
SELECT route, distance, stops 
FROM path;