mysql - 用于从同一列输出两个值的 SQL 语句，如何做到最好？

Question

我正在尝试创建一个 SQL 语句来输出(depart_time、arrival_time、max_passengers、current_passengers 和第二个表中名称列中的两个值)。

我正在创建一个旅行网页，您可以在其中预订预定城市之间的旅行。我现在遇到的问题是，我无法从名称列中输出两个值(例如出发城市和到达城市)。当我尝试“SQL 语句”下列出的代码时，同一个城市被输出两次，如“结果”标签下所示。

该程序允许您选择两个城市(从下拉菜单中)，我将代码中的变量保存为“fromCity”和“toCity”。第一个是“Lissabon”，第二个是“Madrid”。但是，当我运行该语句时，我将 Lissabon 列出了两次。

我已经苦苦挣扎了几个小时，似乎找不到正确的解决方案。

感谢任何帮助!

最好的问候，维克多

表格:

行程表

+---------+-------------+--------------+----------------+--------------------+
| trip_id | depart_time | arrival_time | max_passengers | current_passengers |
+---------+-------------+--------------+----------------+--------------------+
|       1 | 2016-07-20  | 2016-07-21   |             50 |                  0 |
|       2 | 2016-07-25  | 2016-07-26   |             70 |                  0 |
|       3 | 2016-07-26  | 2016-07-27   |             70 |                  0 |
|       4 | 2016-07-27  | 2016-07-28   |             70 |                  0 |
|       5 | 2016-07-29  | 2016-07-30   |             45 |                  0 |
|       6 | 2016-07-30  | 2016-07-30   |             45 |                  0 |
|       7 | 2016-07-31  | 2016-08-02   |             50 |                  0 |
|       8 | 2016-08-02  | 2016-08-04   |             80 |                  0 |
|       9 | 2016-08-04  | 2016-08-06   |             80 |                  0 |
+---------+-------------+--------------+----------------+--------------------+

城市表

+---------+--------------+----------------+------------+--------------+
| city_id | currency     | country        | name       | lang         |
+---------+--------------+----------------+------------+--------------+
|       2 | Svensk Krona | Sverige        | Malmoe     | Svenska      |
|       3 | Svensk Krona | Sverige        | Stockholm  | Svenska      |
|       4 | Dansk Krona  | Danmark        | Koepenhamn | Danska       |
|       5 | Euro         | Tyskland       | Berlin     | Tyska        |
|       6 | Euro         | Spanien        | Madrid     | Spanska      |
|       7 | Euro         | Portugal       | Lissabon   | Portugisiska |
|       8 | Polsk Zloty  | Polen          | Warszawa   | Polska       |
|       9 | Euro         | Frankrike      | Paris      | Franska      |
|      10 | Euro         | Nederländerna  | Amsterdam  | Holländska   |
+---------+--------------+----------------+------------+--------------+

SQL 语句:

SELECT trips.depart_time, trips.arrival_time, trips.max_passengers, 
trips.current_passengers, 
cities.name AS fromCity, cities.name AS toCity
        FROM trips
        INNER JOIN arrivals
        ON arrivals.trip_id = trips.trip_id
        INNER JOIN departures
        ON arrivals.trip_id = departures.trip_id
        INNER JOIN cities
        ON departures.city_id = cities.city_id
        WHERE departures.city_id = 7
        AND arrivals.city_id = 6
        AND trips.depart_time = "2016-07-20"
        GROUP BY name

结果:

+-------------+--------------+----------------+--------------------+----------+----------+
| depart_time | arrival_time | max_passengers | current_passengers | fromCity | toCity   |
+-------------+--------------+----------------+--------------------+----------+----------+
| 2016-07-20  | 2016-07-21   |             50 |                  0 | Lissabon | Lissabon |
+-------------+--------------+----------------+--------------------+----------+----------+

编辑:

感谢彼得。 M，解决方法是:

SELECT trips.depart_time, trips.arrival_time, trips.max_passengers, 
    trips.current_passengers, c1.name, c2.name
            FROM trips
            INNER JOIN arrivals
            ON arrivals.trip_id = trips.trip_id
            INNER JOIN departures
            ON arrivals.trip_id = departures.trip_id
            INNER JOIN cities AS c1
            ON departures.city_id = c1.city_id
            INNER JOIN cities AS c2
            ON arrivals.city_id = c2.city_id
            WHERE departures.city_id = 7
            AND arrivals.city_id = 6
            AND trips.depart_time = "2016-07-20"
            GROUP BY c1.name

Answer 1

(在上面的评论中讨论并解决)

对 cities 表进行两个内部连接。

参见:MySQL INNER JOIN Alias