mysql - 如何在MySQL中交叉两个查询

Question

我的数据库中有 3 个表，如下所示:

airlines
+----+-----------------+-------+---------+ 
| Id | Name            | Abbr  | Country |
+----+-----------------+-------+---------+
| 1  | United Airlines | UAL   | USA     |
| 2  | US Airways      | USAir | USA     |
|... | ...             | ...   | ...     |
+----+-----------------+-------+---------+

airports
+--------------+------+-------------+---------------+-------+
| City         | Code | Name        | Country       | CAbbr |
+--------------+------+-------------+---------------+-------+
| Apalachicola | AAF  | Municipal   | United States | US    |
| Abilene      | ABI  | US Airways  | United States | US    |
| ...          | ...  | ...         | ...           | ...   |
+--------------+------+-------------+---------------+-------+

flights
+---------+----------+--------+-------------+
| Airline | FlightNo | Source | Destination |
+---------+----------+--------+-------------+
| 1       | 28       | APG    | ASY         |
| 1       | 44       | CVO    | ACV         |
| ...     | ...      | ...    | ...         |
+---------+----------+--------+-------------+

我正在尝试报告边疆航空和捷蓝航空提供服务的所有机场对。每个机场对必须准确报告一次(如果报告 X,Y 对，则 Y,X 对是多余的，不应报告)。

我相信我了解如何分别预订边疆航类和捷蓝航空航类:

SELECT  ap1.Code, ap2.Code
FROM    flights f, airports ap1, airports ap2, airlines al
WHERE   ap1.Code = f.Source
AND     ap2.Code = f.Destination
AND     f.Airline = al.Id
AND     al.Abbr = 'Frontier'
ORDER BY ap1.Code ASC;

SELECT  ap1.Code, ap2.Code
FROM    flights f, airports ap1, airports ap2, airlines al
WHERE   ap1.Code = f.Source
AND     ap2.Code = f.Destination
AND     f.Airline = al.Id
AND     al.Abbr = 'JetBlue'
ORDER BY ap1.Code ASC;

但我不明白如何获得它们之间的交集。我已经尝试过 LEFT JOIN 和 WHERE EXISTS 但我做错了，因为我得到的东西看起来更像是一个 Union 而不是一个 Intersection。这是我最近的尝试(绝对是错误的)。

SELECT  ap1.Code, ap2.Code AS code
FROM    airports ap1, airports ap2
WHERE 
EXISTS (SELECT  ap1.Code, ap2.Code
        FROM    flights f, airports ap1, airports ap2, airlines al
        WHERE   ap1.Code = f.Source
        AND     ap2.Code = f.Destination
        AND     f.Airline = al.Id
        AND     al.Abbr = 'Frontier') AND
EXISTS (SELECT  ap1.Code, ap2.Code
        FROM    flights f, airports ap1, airports ap2, airlines al
        WHERE   ap1.Code = f.Source
        AND     ap2.Code = f.Destination
        AND     f.Airline = al.Id
        AND     al.Abbr = 'JetBlue')
ORDER BY ap1.Code ASC;

一旦找到交集，我不太确定如何使 X,Y 对和 Y,X 对不同，因为 DISTINCT 不会将元组识别为相同。

任何帮助将不胜感激。

Answer 1

SELECT apPairs.*, ap1.stuff, ap2.stuff
FROM (
   SELECT LEAST(f.Source, f.Destination) AS ap1Code
      , GREATEST(f.Source, f.Destination) AS ap2Code
   FROM airlines AS al
   INNER JOIN flights AS f ON al.Id = f.Airline
   WHERE al.Abbr IN ('Frontier', 'JetBlue')
   GROUP BY ap1Code, ap2Code
   HAVING COUNT(DISTINCT al.Abbr) = 2  -- Served by both airlines
) AS apPairs
INNER JOIN airports AS ap1 ON apPairs.ap1Code = ap1.Code
INNER JOIN airports AS ap2 ON apPairs.ap1Code = ap2.Code
;

我使用 LEAST 和 GREATEST 来合并任何 (X,Y) 和 (Y,X) 冗余；不过，这些函数是 MySQL 特定的，因此在其他 RDBMS 中，您可能需要创建自己的版本。 注意:根据这些数据的使用方式，您可能需要仔细检查 x->y 和 y->x 对确实是冗余的假设；航空公司有可能(至少在现实中)有 X->Y->Z->X 航线，但没有直达 Y->X。

HAVING 子句中的 COUNT(DISTINCT) 确保仅对每个航空公司缩写计数一次，因此 100 个航类不会使数字膨胀。它在查询中的使用可确保您获得由两家航空公司提供服务的机场，而不是任一航空公司。

由于apPairs中的查询已经有了代码，因此您甚至不需要进行外部查询；但我补充说，如果您需要机场表中的信息，但您认为没有必要在问题中指定。