my SQL
Привет, кто нибудь подскажет функцион в mySQL , которая может к (дате отправления поезда) timestamp прибавить атрибут длительность пути в минутах int, чтобы можно было сделать view с атрибутом (время прибытия поезда) ? В сети попадаються функтионен, которые минуты возвращают, но не то что мне нужно.
SELECT MINUTES( '1999-07-13 06:07:12', '1999-07-13 10:07:12' ); SELECT DATEDIFF( minute, '1999-07-13 06:07:12',
'1999-07-13 10:07:12' );
CREATE TABLE flightexecution (
flight_no varchar(7) NOT NULL,
departure_date_time_utc timestamp NOT NULL,
icao_code_origin varchar(4) NOT NULL,
icao_code_destination varchar(4) NOT NULL,
plane_id int NOT NULL,
flight_duration_minutes int NOT NULL,
PRIMARY KEY (flight_no,departure_date_time_utc),
FOREIGN KEY (plane_id) REFERENCES plane (id),
FOREIGN KEY (icao_code_origin) REFERENCES airport (icao_code),
FOREIGN KEY (icao_code_destination) REFERENCES airport (icao_code)
) ;
create view flightexecution_arrival as
select f.flight_no, a.airport_name as von, ar.airport_name as nach, f.departure_date_time_utc as Departure,
f.flight_duration_minutes as Duration, f.departure_date_time_utc + interval f.flight_duration_minutes minute as 'Landing Time'
from flightexecution f, airport a, airport ar
where f.icao_code_origin = a.icao_code and f.icao_code_destination = ar.icao_code
having f.departure_date_time_utc + interval f.flight_duration_minutes minute > now();
Не пойму где ошибка:
Напишу с комментариями:
ДАНО:
CREATE TABLE flightexecution (
flight_no varchar(7) NOT NULL,
departure_date_time_utc timestamp NOT NULL,
icao_code_origin varchar(4) NOT NULL, <---- аеропорт отлет
icao_code_destination varchar(4) NOT NULL, <---- аеропорт прилет
plane_id int NOT NULL,
flight_duration_minutes int NOT NULL, <----длительность пути в минутах
PRIMARY KEY (flight_no,departure_date_time_utc),
FOREIGN KEY (plane_id) REFERENCES plane (id),
FOREIGN KEY (icao_code_origin) REFERENCES airport (icao_code),
FOREIGN KEY (icao_code_destination) REFERENCES airport (icao_code)
) ;
Задание:
Mit welchen 2er Flugkombinationen (also genau einmal Umsteigen) kann man von EDDT wieder nach EDDT fliegen, wobei die jeweilige Flugdauer der einzelnen Flüge maximal 12 Stunden sein soll. Die Umsteigezeit (also der Unterschied von Landezeit und Startzeit für den 2. Flug soll mindestens 4 Stunden und maximal 25 Stunden betragen).
Мой стейтмент:
select f.icao_code_origin, f1.icao_code_destination from flightexecution f, flightexecution f1 where f.icao_code_origin like 'EDDT' and f.icao_code_destination not like 'EDDT'
and f1.icao_code_origin not like 'EDDT' and f.icao_code_destination like 'EDDT' and f.flight_duration_minutes <= 720 and f1.flight_duration_minutes <= 720
having TIMESTAMPDIFF(hour, DATE_ADD(f.departure_date_time_utc, INTERVAL f.flight_duration_minutes MINUTE), f1.departure_date_time_utc) >= 4
and TIMESTAMPDIFF(hour, DATE_ADD(f.departure_date_time_utc, INTERVAL f.flight_duration_minutes MINUTE), f1.departure_date_time_utc)<=25;
А где у тебя агрегирование, чтобы получить подмножество для having clause?
select f1.flight_no, f1.departure_date_time_utc, f2.flight_no, f2.departure_date_time_utc from flightexecution f1 inner join flightexecution f2 on f1.icao_code_destination = f2.icao_code_origin where f1.icao_code_origin = 'EDDT' and f2.icao_code_destination = 'EDDT' and f1.flight_duration_minutes <= 720 and f2.flight_duration_minutes <= 720 and TIMESTAMPDIFF(hour, DATE_ADD(f1.departure_date_time_utc, INTERVAL f1.flight_duration_minutes MINUTE), f2.departure_date_time_utc) BETWEEN 4 AND 25;