问Mysql如何连接这些表
EN

Stack Overflow用户

提问于 2014-04-06 05:58:18

回答 2查看 81关注 0票数 0

我有以下表格和样本数据。

SCHEDULE TABLE
fightno   day_of_week  orgin    dest  depart_time   arrive_time     totalfare
AQ2131    WED          BLR      KTA   04:30         11:00           6000

FLIGHT_STOPS (if it have stops)
flightno  day_of_week  airport_code   arrival_time  departure_time  stopmilesfare
AQ2131    WED          BOM            02:17         05:40           3000 
AQ2131    WED          COH            03:17         06:40           3000 
AQ2131    WED          GOY            04:17         08:40           3000

航线为BLR -> BOM -> COH -> GOY -> KTA

我如何加入这些表，以便如果用户输入其中任何一个作为源和目标，则检索flightno。例如，如果用户输入BLR作为源，COH作为目的地，COH作为源，GOY作为目的地，我们可以找到航班号。

mysql

平台电商解决方案

为客户提供电商客服、电商图片系统识别及搜索的快速搭建方案

回答 2

Stack Overflow用户

回答已采纳

发布于 2014-04-07 04:13:31

首先，让我们退一步。航班只是从A到D的一个方向，或D到A的方向，这两个不同的航班号。你必须有正确的方向相关性才能得到正确的飞行。笑一笑，想象中的国脚

Flight #123 From Country A -> B -> C -> D.
Flight #987 From Country D -> C -> B -> A.

两次旅行都有“C”和“B”..。但是如果我们的旅程是从'C‘开始，以'B’结束的话，你需要987次航班，因为你需要在'C‘的飞机上，然后在'B’停--在那个方向。123号航班会失败，因为你会在'A‘下在'B’和到达'C‘站被接上。我将使用'CCC‘和'BBB’只是为了遵守3字符机场代码与查询。

第一个查询(它将是UNION的一部分)获取从给定位置开始的所有航班.无论是目的地还是行程的其中一条腿，目的地都是所需的'BBB‘位置。通过在相同的航班和目的地机场代码上使用左/连接，如果它不是空的，那么就有一条腿到那个目的地。

SELECT 
      s1.flightNo
   from 
      schedule s1
         LEFT JOIN flight_stops fs1
            ON s1.flightNo = fs1.flightNo
            AND fs1.airport_code = 'BBB'
   where 
          s1.origin = 'CCC'
      AND (    s1.dest = 'BBB'
           OR fs1.flightNo IS NOT NULL )

下一个查询( UNION ALL one)将得到从其他地方开始的任何航班，该航班有一条腿作为旅程的开始飞行(CCC)。然后，它将加入进度表，看看航班的目的地是否是“BBB”。如果不是那样的话，那么也可以再看一次航班停止表，但特别是对于“BBB”来说，“BBB”的到达时间在“CCC”开始飞行时间之后。

SELECT 
      fs1.flightNo
   from 
      flight_stops fs1
         LEFT JOIN schedule s2
             ON fs1.flightNo = s2.flightNo
            AND s2.dest = 'BBB'

         LEFT JOIN flight_stops fs2
             ON fs1.flightNo = fs2.flightNo
            AND fs1.arrival_time < fs2.arrival_time
            AND fs2.airport_code = 'BBB'
   where 
      fs1.airport_code = 'CCC'

既然我们得到的只是航班号，那就把这两个航班联合起来。既然一架飞机不能同时起飞，又不能同时成为一条腿，他们就永远不会拉同一架飞机。如果你想要实际的其余的航班信息，你也可以结束。

因此，对航班的最后查询将是

(first select above)
UNION ALL
(second select above)

有航标信息..。

SELECT
      s.*
   from
      ( (first select above)
         UNION ALL
         (second select above) ) QualFlights
      JOIN Schedule s
         ON QualFlights.flightNo = s.flightNo

现在，所有这一切，如果您的flight_stops表包含了原产地和目的地位置，就会容易得多。然后，整个查询可以简化为并在flight_stops上有一个索引.(airport_code、flightNo、arrival_time )

  SELECT 
          fs1.flightNo
       from 
          flight_stops fs1
             LEFT JOIN flight_stops fs2
                 ON fs1.flightNo = fs2.flightNo
                AND fs1.arrival_time < fs2.arrival_time
                AND fs2.airport_code = 'BBB'
       where 
          fs1.airport_code = 'CCC'

是的..。这是一个查询，您基本上需要的是在table表的“腿”中同时包含起始点和目的地。

票数 1

Stack Overflow用户

发布于 2014-04-06 08:25:07

这是使用CTE和ROW_NUMBER来代表缺少的LegNo的TSQL。我现在没有时间把它翻译成MySQL，但是在LegNo已经存在的情况下，这是非常容易的。

另外，ISNULL(x，y)不是MySQL，但是当x为NULL时，则应该转换为y。

CREATE TABLE Schedule (FlightNo CHAR(6), Week_Day CHAR(3), Origin CHAR(3), Dest CHAR(3), Time_Depart Time, Time_Arrive Time)
CREATE TABLE Flight_Stop (FlightNo CHAR(6), Week_Day CHAR(3), Airport CHAR(3), Time_Arrive Time, Time_Depart Time)

INSERT INTO Schedule VALUES 
 ('AQ2131','WED','BLR','KTA','01:30','11:00')
,('AB1234','TUE','AAA','BBB','01:00','02:00')
INSERT INTO Flight_Stop VALUES 
 ('AQ2131','WED','BOM','02:17','05:40')
,('AQ2131','WED','COH','03:17','06:40')
,('AQ2131','WED','GOY','04:17','08:40')

WITH cte AS (
  SELECT *
        ,ROW_NUMBER() OVER (PARTITION BY FlightNo, Week_Day ORDER BY Time_Arrive) LegNo 
    FROM Flight_Stop
)
SELECT s.FlightNo
      ,s.Week_Day
      ,s.Origin
      ,ISNULL(f.Airport,s.Dest) Dest
      ,0 LegNo
  FROM Schedule s
       LEFT JOIN
       cte f ON s.FlightNo = f.FlightNo
            AND s.Week_Day = f.Week_Day
 WHERE ISNULL(f.LegNo,1) = 1
UNION 
SELECT s.FlightNo
      ,s.Week_Day
      ,s.Origin
      ,ISNULL(f.Airport,s.Dest) Dest
      ,f.LegNo
  FROM Schedule s
       LEFT JOIN
       cte f ON s.FlightNo = f.FlightNo
            AND s.Week_Day = f.Week_Day
 WHERE f.LegNo = (SELECT MAX(LegNo)
                    FROM cte a 
                   WHERE a.FlightNo = f.FlightNo
                     AND a.Week_Day = f.Week_Day)
UNION
SELECT a.FlightNo
      ,a.Week_Day
      ,a.Airport
      ,b.Airport
      ,a.LegNo
  FROM cte a
       INNER JOIN
       cte b ON a.FlightNo = b.FlightNo
            AND a.Week_Day = b.Week_Day
            AND a.LegNo + 1 = b.LegNo