mySQL，特定列的 WHERE 语句

Question

我必须选择从“慕尼黑”飞往“杜塞尔多夫”并返回的所有航类。表 Flightexecutions 包含查找正确航类的信息。

根据如下所示的我的声明，我只能选择“来自”的航类号和机场。

我不知道如何设置 SELECT 语句以获得包含 FlightNo、from 和 to 列的所需输出。

--1)statements works correct, returns all FlightNo and origin "from"
SELECT flx.FlightNo, air.AirportName as `from`, air.AirportName as `to`
FROM flightexecution as flx, airport.airport as air
WHERE flx.ICAO_Code_Origin = air.ICAO_Code and (air.AirportName LIKE 'München (Franz Josef Strauß)'
OR air.AirportName LIKE 'Düsseldorf International');

--2)returns all flightNo and destination "to"
SELECT flx.FlightNo, air.AirportName as `to`
FROM flightexecution as flx, airport.airport as air
WHERE flx.ICAO_Code_Destination = air.ICAO_Code and (air.AirportName LIKE 'München (Franz Josef Strauß)'
      OR air.AirportName LIKE 'Düsseldorf International');

output1):

    FlightNo | from 
    -----------------
             |   

output2):

    FlightNo | to 
    -----------------
             |    




desired output:

FlightNo | from | to 
-------------------------
         |      |  

ERR-Model:

Answer 1

这应该可以做到；它将所有航类执行独立地连接到其出发地和目的地机场，然后使用 WHERE 条件将连接结果过滤为仅具有所需端点的结果。

SELECT fx.FlightNo, o.AirportName AS `from`, d.AirportName AS `to`
FROM flightexecution AS fx
INNER JOIN airport AS o ON fx.ICAO_Code_Origin = o.ICAO_Code
INNER JOIN airport AS d ON fx.ICAO_Code_Destination = d.ICAO_Code
WHERE (o.AirportName = 'München (Franz Josef Strauß)' AND d.AirportName = 'Düsseldorf International')
   OR (d.AirportName = 'München (Franz Josef Strauß)' AND o.AirportName = 'Düsseldorf International')

旁注:由于 MySQL 处理 OR 条件的方式(糟糕)；如果数据库很大，通过联合此查询的两个版本(一个版本与 WHERE 条件的每个 OR 部分)可能会获得显着更好的性能。