SQLize | PHPize | SQLtest

WITH all_possible_pairs AS ( -- Все возможные пары разных аэропортов SELECT d.airport_code AS dep_code, d.airport_name->>'en' AS departure_airport, a.airport_code AS arr_code, a.airport_name->>'en' AS arrival_airport FROM airports_data d CROSS JOIN airports_data a WHERE d.airport_code <> a.airport_code ), existing_routes AS ( -- Существующие маршруты (прямые рейсы) SELECT DISTINCT departure_airport AS dep_code, arrival_airport AS arr_code FROM flights ) -- Пары аэропортов без прямого сообщения SELECT p.departure_airport, p.arrival_airport FROM all_possible_pairs p LEFT JOIN existing_routes r ON p.dep_code = r.dep_code AND p.arr_code = r.arr_code WHERE r.dep_code IS NULL ORDER BY p.departure_airport, p.arrival_airport; ; select * from query_cost('WITH all_possible_pairs AS ( -- Все возможные пары разных аэропортов SELECT d.airport_code AS dep_code, d.airport_name->>''en'' AS departure_airport, a.airport_code AS arr_code, a.airport_name->>''en'' AS arrival_airport FROM airports_data d CROSS JOIN airports_data a WHERE d.airport_code <> a.airport_code ), existing_routes AS ( -- Существующие маршруты (прямые рейсы) SELECT DISTINCT departure_airport AS dep_code, arrival_airport AS arr_code FROM flights ) -- Пары аэропортов без прямого сообщения SELECT p.departure_airport, p.arrival_airport FROM all_possible_pairs p LEFT JOIN existing_routes r ON p.dep_code = r.dep_code AND p.arr_code = r.arr_code WHERE r.dep_code IS NULL ORDER BY p.departure_airport, p.arrival_airport;');