Forum Webscript.Ru
Общие => Базы данных => Тема начата: commander от 19 Ноября 2004, 17:37:05
-
Вопросик такой:
БД- PostgreSQL.
Дано:
-------------------------------------------------------------------------------------
У меня есть две связанные таблицы:
CREATE TABLE train_main
(
train_main_id serial,
tr_num varchar(150),
date_arr date,
date_dep date,
CONSTRAINT train_main_pk PRIMARY KEY (train_main_id)
);
CREATE TABLE train_plase
(
train_plase_id serial,
train_main_id int4,
train_city_id int4,
num_ost int4,
CONSTRAINT train_plase_pk PRIMARY KEY (train_plase_id)
);
В первой содержиться номер поезда и даты начала движ. поезда и конца движ.
Во второй города следования поездов (train_city_id) и порядок их посещения
поездом (num_ost = 0 - начальный пункт, Max(num_ost) - конечный пункт).
-------------------------------------------------------------------------------------
Задача:
-------------------------------------------------------------------------------------
Вопрос в том как составить SQL запрос поиска поля train_main_id, зная город отправления и город прибытия, соблюдая порядок следования станций (num_ost).
-------------------------------------------------------------------------------------
-
$A - пункт отправления
$B - пункт прибытия
тогда
список поездов, выходящих из пункта А
SELECT train_main_id FROM train_place
WHERE train_place_id = $A AND num_ost = 0
получаем num_ost последней остановки для поездов выходящих из пункта А
SELECT train_main_id, MAX(num_ost) as max_ost FROM train_place
WHERE train_main_id IN (
SELECT train_main_id FROM train_place
WHERE train_place_id = $A AND num_ost = 0
) GROUP BY train_main_id
Результат
SELECT train_main_id FROM train_place, (
SELECT train_main_id, MAX(num_ost) as max_ost FROM train_place
WHERE train_main_id IN (
SELECT train_main_id FROM train_place
WHERE train_place_id = $A AND num_ost = 0
) GROUP BY train_main_id
) as tmp
WHERE
train_place.train_main_id = tmp.train_main_id AND
train_place.num_ost = tmp.max_ost AND
train_place.train_place_id = $B
:)
это все догадки, запросы не проверял и давно с PG не работал
-
Макс
Идея мне понятна, но... фокус в том что Пункт $A не всегда являеться начальной остановкой (num_ost = 0), точно так же как и пункт $B не всегда MAX(num_ost)... Пример:
поезд № 001 СПб - МСК имеет так же остановки Тула и Балагое, И при поиске поезда идущего из Тулы в Балагое поезд 001 должен тоже найтись...
-
Уфф... сам дочухал....
Идея такая:
Все поезда выходящие из пункта $A
SELECT DISTINCT train_main_id FROM train_plase WHERE train_city_id = $A
Все поезда соединяющие пункт $A и $B.
SELECT DISTINCT train_main_id FROM train_plase WHERE train_main_id IN (SELECT DISTINCT train_main_id FROM train_plase WHERE train_city_id = $A) AND train_city_id=$B
Все поезда и номера остановок пункта $B:
(SELECT DISTINCT train_main_id AS tm_id, num_ost AS n_o FROM train_plase WHERE train_city_id = 34) AS tmp
Общий результат:
SELECT DISTINCT train_main_id FROM train_plase,
(SELECT DISTINCT train_main_id AS tm_id, num_ost AS n_o FROM train_plase WHERE train_city_id = 34) AS tmp
WHERE train_main_id IN (SELECT DISTINCT train_main_id FROM train_plase WHERE train_city_id = 34 )
AND train_city_id=35 AND num_ost>tmp.n_o AND train_main_id=tmp.tm_id
-
Если кому-нить интересно в результате получилось:
---------------------------------------------------------------------------------------------------
SELECT DISTINCT m.train_main_id, m.tr_num, m.date_arr, m.date_dep,(SELECT name_eng FROM train_city WHERE train_city_id=\'$city_forward\') AS city_forward,(SELECT s.name_eng FROM train_plase p, train_station s WHERE p.train_city_id=\'$city_forward\' AND train_main_id=m.train_main_id AND p.train_station_id=s.train_station_id) AS station_forward,(SELECT time FROM train_plase WHERE train_city_id=\'$city_forward\' AND train_main_id=m.train_main_id) AS time_forward, (SELECT name_eng FROM train_city WHERE train_city_id=\'$city_back\') AS city_back,(SELECT s.name_eng FROM train_plase p, train_station s WHERE p.train_city_id=\'$city_back\' AND train_main_id=m.train_main_id AND p.train_station_id=s.train_station_id) AS station_forward,(SELECT time FROM train_plase WHERE train_city_id=\'$city_back\' AND train_main_id=m.train_main_id) AS time_back FROM train_main m, train_plase p, (SELECT DISTINCT train_main_id AS tm_id, num_ost AS n_o FROM train_plase WHERE train_city_id = $city_forward) AS tmp WHERE p.train_main_id IN (SELECT DISTINCT train_main_id AS train_main_id_001 FROM train_plase WHERE train_city_id = $city_forward) AND p.train_city_id=$city_back AND p.num_ost>tmp.n_o AND p.train_main_id=tmp.tm_id AND m.train_main_id=p.train_main_id AND m.date_arr<=\'$date_forward\' AND m.date_dep>=\'$date_forward\'
---------------------------------------------------------------------------------------------------
И это один МЕГА запрос! :))) 1301 - символ... :)