我正在做一个家庭作业项目，我应该执行一个数据库查询，通过城市名称或机场代码查找航班，但 flights 表只包含机场代码，所以如果我想按城市搜索我必须加入 airports 表。
机场表具有以下列：code, city
航班表具有以下列：airline, flt_no, fairport, tairport, depart, arrive, fare
fairport 和 tairport 列是 from 和 to 机场代码。
depart 和 arrive 列是出发和到达日期。
我想出了一个查询，它首先连接 fairport 列和 airports.code 列上的航班。为了让我匹配 tairport，我必须对第一次加入的先前匹配执行另一次加入。
SELECT airline, flt_no, fairport, tairport, depart, arrive, fare
FROM (SELECT * FROM flights
INNER JOIN airports
ON flights.fairport = airports.code
WHERE (airports.code = '?' OR airports.city='?')) AS matches
INNER JOIN airports
ON matches.tairport = airports.code
WHERE (airports.code = '?' OR airports.city = '?')
我的查询返回了正确的结果，它足以满足家庭作业的目的，但我想知道我是否可以在多个列上JOIN？我将如何构建 WHERE 子句以使其与出发地和目的地城市/代码相匹配？
下面是关于我想要实现的“伪查询”，但我无法正确获取语法，并且我不知道如何表示出发和目的地的 airports 表：
SELECT * FROM flights
INNER JOIN airports
ON flights.fairport = airports.code AND flights.tairport = airports.code
WHERE (airports.code = 'departureCode' OR airports.city= 'departureCity')
AND (airports.code = 'destinationCode' OR airports.city = 'destinationCity')
更新
我还发现 this visual representation of SQL Join statements 作为关于如何构造 SQL 语句的一般指南非常很有帮助！
您可以通过为连接的表指定 alias 来多次连接同一个表，如下例所示：
SELECT
airline, flt_no, fairport, tairport, depart, arrive, fare
FROM
flights
INNER JOIN
airports from_port ON (from_port.code = flights.fairport)
INNER JOIN
airports to_port ON (to_port.code = flights.tairport)
WHERE
from_port.code = '?' OR to_port.code = '?' OR airports.city='?'
请注意，to_port 和 from_port 是 airports 表的第一个和第二个副本的别名。
为什么不能只在 ON 子句中使用 AND？例如：
SELECT *
FROM flights
INNER JOIN airports
ON ((airports.code = flights.fairport)
AND (airports.code = flights.tairport))
就像是....
SELECT f.*
,a1.city as from
,a2.city as to
FROM flights f
INNER JOIN airports a1
ON f.fairport = a1.code
INNER JOIN airports a2
ON f.tairport = a2.code
flights f INNER JOIN airports a ON a.code = f.fairport OR a.code = f.tairport 它有什么不同吗？你怎么看？
如果mysql适合你：
SELECT flights.*,
fromairports.city as fromCity,
toairports.city as toCity
FROM flights
LEFT JOIN (airports as fromairports, airports as toairports)
ON (fromairports.code=flights.fairport AND toairports.code=flights.tairport )
WHERE flights.fairport = '?' OR fromairports.city = '?'
编辑：添加示例以过滤代码或城市的输出
airports.code 而不是 fromairports.code 和 toairports.code？这样您就不需要 as fromairports, airports as toairports。
如果您想同时搜索 FROM 和 TO 机场，您需要加入 Airports 表两次 - 然后您可以在结果集中同时使用 from 和 to 表：
SELECT
Flights.*,fromAirports.*,toAirports.*
FROM
Flights
INNER JOIN
Airports fromAirports on Flights.fairport = fromAirports.code
INNER JOIN
Airports toAirports on Flights.tairport = toAirports.code
WHERE
...
SELECT *
FROM flights
INNER JOIN airports
ON ((airports.code = flights.fairport)
OR (airports.code = flights.tairport))
OR 可以在上面的 JOIN 条件中使用吗
flights f INNER JOIN airports a ON a.code = f.fairport OR a.code = f.tairport请提出建议。