时间戳

首页 > 解决方案 > 如何在 MySQL 中交叉两个查询

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

问题描述

我在数据库中有 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         |
| ...     | ...      | ...    | ...         |
+---------+----------+--------+-------------+

我正在尝试报告 Frontier 和 JetBlue 服务的所有机场对。每个机场对必须准确报告一次（如果报告了一对 X,Y，则一对 Y,X 是多余的，不应报告）。

我相信我了解如何分别获得 Frontier 航班和 JetBlue 航班：

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 但我做错了，因为我得到的东西看起来更像是一个联合而不是一个交叉点。这是我最近的尝试（绝对错误）。

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 不会将元组识别为相同的。

任何帮助将非常感激。

标签： mysqlintersection

解决方案

如果您需要两者，那么您可以尝试将 IN 用于航空公司：

SELECT  ap1.Code, ap2.Code
FROM    flights f
INNER JOIN airports ap1 ON  ap1.Code = f.Source
INNER JOIN airports ap2 ON ap2.Code = f.Destination
INNER JOIN airlines al ON  f.Airline = al.Id
WHERE  al.Abbr IN ( 'Frontier', 'JetBlue')
ORDER BY ap1.Code ASC;

您应该避免基于 where 的旧 (1992) 隐式连接语法，并使用显式连接语法。

推荐阅读