SQL上一行，下一行 [英] SQL previous, next row

问题描述

2张桌子，一张带有航班，一张带有目的地. 我希望所有在表2中有出发和到达的航班(为我工作).但是我想让到达跟随出发.看例子 谢谢所有

2 table, one with flights, one with destination. I want all flights with departure and arrival who are in the table 2 (work for me). But i want so that arrival follow departure. See example Thanks for all

CREATE TABLE flights
        (`Name` varchar(10), `Departure` varchar(50), `Arrival` varchar(10), `Pass` int, `Cargo` int, `Dist` int)
    ;

    INSERT INTO flights
        (`Name`, `Departure`, `Arrival`, `Pass`, `Cargo`, `Dist`)
    VALUES
        ('444 737vvv', 'LFLL gee', 'LPMAdsf', 200, 2000, 12),
        ('gg737vvv', 'LPMA-egege', 'LFLLdsf', 3000, 0, 13),
        ('747vvv', 'LFLLèèegege', 'LPMAdsf', 0, 5000, 15),
        ('747vvv', 'OTHHèèegege', 'LPMAdsf', 0, 5000, 15),
        ('747vvv', 'OMDBèèegege', 'LPMAdsf', 0, 5000, 15),
        ('a320vvv', 'EGKK-egege', 'LFPOdd', 0, 6000, 14)
    ;



    CREATE TABLE Regular
        (`Dep` varchar(21), `Arri` varchar(21),`Type` varchar(21))
    ;

    INSERT INTO Regular
        (`Dep`, `Arri`, `type`)
    VALUES
        ('LFLL', 'LFPG', 'cargo'),
        ('LFPG', 'LFLL', 'cargo'),
        ('LFLL', 'LPMA', 'com'),
        ('LPMA', 'LFLL', 'cargo'),
        ('LFPO', 'EGKK', 'cargo'),
        ('EGKK', 'LFPO', 'com')
    ;

根据此请求，我可以在常规表中查看所有出发和到达"航班.好的！但是我只希望只有到达后才出发的航班.

With this request i have all flight where Departure et Arrival are in Regular table. Good! But i want so only flight who arrival follow departure.

今天的示例:

 $query = "Select
    flights.UserName,
    flights.FlightDate,
    flights.FlightResult,
    flights.AircraftName,
    flights.DepartureIcaoName,
    flights.ArrivalIcaoName
    from
    flights
    inner join regular
    on regular.Departure = SUBSTRING(flights.DepartureIcaoName,1,4) and regular.Arrival = SUBSTRING(flights.ArrivalIcaoName,1,4);";

我有:

('444 737vvv', 'LFLL gee', 'LPMAdsf', 200, 2000, 12),
    ('gg737vvv', 'LPMA-egege', 'LFLLdsf', 3000, 0, 13),
    ('747vvv', 'LFLLèèegege', 'LPMAdsf', 0, 5000, 15),
    ('a320vvv', 'EGKK-egege', 'LFPOdd', 0, 6000, 14)

我想要:

('444 737vvv', 'LFLL gee', 'LPMAdsf', 200, 2000, 12),
    ('gg737vvv', 'LPMA-egege', 'LFLLdsf', 3000, 0, 13),
    ('747vvv', 'LFLLèèegege', 'LPMAdsf', 0, 5000, 15),

我想(但不起作用):

$query = "Select 
flights.*, regular.* 
from flights, regular 
where regular.Departure = SUBSTRING(flights.DepartureIcaoName,1,4) 
and regular.Arrival = SUBSTRING(flights.ArrivalIcaoName,1,4) 
and SUBSTRING(flights.DepartureIcaoName,1,4) = (SUBSTRING(flights.ArrivalIcaoName,1,4) - 1)
";

推荐答案

进行查询的另一种方法是:

A different way to make your query is:

SELECT
    flights.name,
    flights.Departure
FROM
    flights
    INNER JOIN Table2
        ON CONCAT(Table2.Dep,Table2.Arri) = CONCAT(SUBSTRING(flights.Departure,1,4),SUBSTRING(flights.Arrival,1,4));

但是产生相同的结果.

问题是，在您的示例中，航班a320vvv在航班中具有离港"夫妇字符串EGKK LFPO，而在表2中，您同时拥有EGKK LFPO和LFPO EGKK.这样查询就做对了.

The problem is that in your example the flight a320vvv has a Departure-Arrival couple string EGKK LFPO in flights and in Table2 you have both EGKK LFPO and LFPO EGKK. So the query is doing right.

实际上，如果您进行SELECT *，您会看到结果是:

Indeed, if you make SELECT * you see that the result is:

其中所选航班是EGKK LFPO出发到达的com航班，而不是有LFPO EGKK出发到达的货运的航班.

where the selected flight is the com one, with EGKK LFPO departure arrival, and not the cargo one, that has departure arrival LFPO EGKK.

致谢

这篇关于SQL上一行，下一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！