SQL上一行，下一行

本文介绍了SQL上一行，下一行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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.

致谢