我们可以尝试在 flight 表，条件是第一个目的地是第二条路线的起点。此外，我们可以限制出发地和最终目的地城市。请注意，我们也加入到每个 flight table departure 桌上，带上出发日期。

SELECT
    d1.date AS date_start,
    f1.flightnr AS flightnr_start,
    f1.start AS first_start,
    f1.dest AS change_over_dest,
    d2.date AS date_end,
    f2.flightnr AS flightnr_end,
    f2.start AS last_start,
    f2.dest AS change_over_end
FROM flight f1
INNER JOIN departure d1
    ON f1.flightnr = d1.flightnr
INNER JOIN flight f2
    ON f1.dest = f2.start
INNER JOIN departure d2
    ON f2.flightnr = d2.flightnr AND d1.date = d2.date
WHERE
    f1.start = 'FRA' AND f2.dest = 'PEK';

我没有费心围绕您的完整样本数据集构建一个演示，因为它充满了拼写错误。相反，我只是设置了一个简单的演示，下面，这似乎是工作。

演示

DROP TABLE IF EXISTS fight;

CREATE TABLE flight
(flightnr CHAR(5) NOT NULL PRIMARY KEY
,destination CHAR(3) NOT NULL
,start CHAR(3) NOT NULL
);

INSERT INTO flight VALUES
('LH100','ATL','FRA'),
('LH102','DXB','FRA'),
('LH103','ORD','FRA'),
('LH104','HND','FRA'),
('LH105','LHR','FRA'),
('LH106','LAX','FRA'),
('LH107','HKG','FRA'),
('LH108','CDG','FRA'),
('LH109','DFW','FRA'),
('LH110','IST','FRA'),
('LH200','ATL','LHR'),
('LH201','PEK','LHR'),
('LH202','DXB','LHR'),
('LH203','ORD','LHR'),
('LH204','FRA','LHR');

DROP TABLE IF EXISTS departure;

CREATE TABLE departure
(flightnr CHAR(5) NOT NULL
,date DATE NOT NULL
,sign CHAR(6) NOT NULL
,PRIMARY KEY(flightnr,date)
);

INSERT INTO departure VALUES
('LH100','2018-10-01','D-ABBL'),
('LH100','2018-10-02','D-ABBL'),
('LH100','2018-10-03','D-ABBL'),
('LH100','2018-10-04','D-ABBL'),
('LH100','2018-10-05','D-ABBL'),
('LH100','2018-10-06','D-ABBL'),
('LH100','2018-10-07','D-ABBK'),
('LH100','2018-10-08','D-ABBK'),
('LH102','2018-10-02','D-ABBB'),
('LH102','2018-10-04','D-ABBB'),
('LH102','2018-10-06','D-ABBB'),
('LH103','2018-10-09','D-ABBB'),
('LH104','2018-10-03','D-ABBB'),
('LH104','2018-10-05','D-ABBB'),
('LH104','2018-10-07','D-ABBB'),
('LH104','2018-10-09','D-ABBB'),
('LH105','2018-10-01','D-ABBB'),
('LH105','2018-10-02','D-ABBB'),
('LH105','2018-10-03','D-ABBB'),
('LH200','2018-10-01','D-ABCA'),
('LH200','2018-10-02','D-ABCA'),
('LH201','2018-10-03','D-ABCA'),
('LH201','2018-10-05','D-ABBR'),
('LH202','2018-10-05','D-ABCA'),
('LH202','2018-10-09','D-ABCA'),
('LH203','2018-10-04','D-ABCA'),
('LH203','2018-10-05','D-ABCA'),
('LH203','2018-10-06','D-ABCC'),
('LH204','2018-10-01','D-ABBF'),
('LH204','2018-10-03','D-ABBI'),
('LH204','2018-10-08','D-ABBM');

SELECT f1.flightnr
     , f1.start
     , d1.date startdate
     , f2.flightnr transferflightnr
     , f2.start transferlocation
     , d2.date transferdate
     , f2.destination 
  FROM flight f1 
  JOIN departure d1 
    ON d1.flightnr = f1.flightnr 
  JOIN flight f2 
    ON f2.start = f1.destination 
  JOIN departure d2 
    ON d2.flightnr = f2.flightnr 
   AND d2.date >= d1.date 
 WHERE f2.destination = 'pek' 
   AND f1.start = 'fra';
+----------+-------+------------+------------------+------------------+--------------+-------------+
| flightnr | start | startdate  | transferflightnr | transferlocation | transferdate | destination |
+----------+-------+------------+------------------+------------------+--------------+-------------+
| LH105    | FRA   | 2018-10-01 | LH201            | LHR              | 2018-10-03   | PEK         |
| LH105    | FRA   | 2018-10-02 | LH201            | LHR              | 2018-10-03   | PEK         |
| LH105    | FRA   | 2018-10-03 | LH201            | LHR              | 2018-10-03   | PEK         |
| LH105    | FRA   | 2018-10-01 | LH201            | LHR              | 2018-10-05   | PEK         |
| LH105    | FRA   | 2018-10-02 | LH201            | LHR              | 2018-10-05   | PEK         |
| LH105    | FRA   | 2018-10-03 | LH201            | LHR              | 2018-10-05   | PEK         |
+----------+-------+------------+------------------+------------------+--------------+-------------+