MySQL для выбора расписания транспорта

Я разрабатываю базу данных MySQL для обработки расписаний транспорта. Каждая поездка на поезде или автобусе имеет уникальный идентификатор, и поездка из start в finish может не включать всех возможных точек остановки.

У меня есть три таблицы: расписание, вокзал и проезд. Последние два в настоящее время содержат только идентификатор автоинкремента и идентификатор varchar. Таблица расписания содержит идентификатор автоинкремента, s_id и j_id (относящиеся к станции и идентификатору поездки соответственно), stop_order и время прибытия / отправления.

Запуск этого SQL:

SELECT schedules.id, journeys.journey, stations.station, schedules.s_order, 
schedules.a_time, schedules.d_time

FROM `schedules` 
JOIN `journeys` ON schedules.j_id = journeys.j_id
JOIN `stations` ON schedules.s_id = stations.s_id

возвращает этот результат:

+----+---------+---------+---------+----------+----------+
| id | journey | station | s_order | a_time   | d_time   |
+----+---------+---------+---------+----------+----------+
|  1 | J1      | STA     |       1 | NULL     | 07:00:00 |
|  2 | J1      | STB     |       2 | 07:09:00 | 07:10:00 |
|  3 | J1      | STC     |       3 | 07:29:00 | 07:30:00 |
|  4 | J1      | STD     |       4 | 07:44:00 | 07:45:00 |
|  5 | J1      | STE     |       5 | 07:59:00 | 08:00:00 |
|  6 | J1      | STF     |       6 | 08:15:00 | NULL     |
|  7 | J2      | STA     |       1 | NULL     | 07:30:00 |
|  8 | J2      | STC     |       2 | 07:59:00 | 08:00:00 |
|  9 | J2      | STF     |       3 | 08:45:00 | NULL     |
| 10 | J3      | STA     |       1 | NULL     | 08:00:00 |
| 11 | J3      | STB     |       2 | 08:09:00 | 08:10:00 |
| 12 | J3      | STD     |       3 | 08:44:00 | 08:45:00 |
| 13 | J3      | STE     |       4 | 09:00:00 | NULL     |
| 14 | J4      | STA     |       1 | NULL     | 08:30:00 |
| 15 | J4      | STD     |       2 | 09:14:00 | 09:15:00 |
| 16 | J4      | STF     |       3 | 09:45:00 | NULL     |
| 17 | J5      | STF     |       1 | NULL     | 07:10:00 |
| 18 | J5      | STE     |       2 | 07:24:00 | 07:25:00 |
| 19 | J5      | STD     |       3 | 07:39:00 | 07:40:00 |
| 20 | J5      | STC     |       4 | 07:54:00 | 07:55:00 |
| 21 | J5      | STB     |       5 | 08:14:00 | 08:15:00 |
| 22 | J5      | STA     |       6 | 08:25:00 | NULL     |
| 23 | J6      | STF     |       1 | NULL     | 07:30:00 |
| 24 | J6      | STC     |       2 | 08:14:00 | 08:15:00 |
| 25 | J6      | STA     |       3 | 08:45:00 | NULL     |
| 26 | J7      | STE     |       1 | NULL     | 08:00:00 |
| 27 | J7      | STD     |       2 | 08:14:00 | 08:15:00 |
| 28 | J7      | STB     |       3 | 08:49:00 | 08:50:00 |
| 29 | J7      | STA     |       4 | 09:00:00 | NULL     |
| 30 | J8      | STF     |       1 | NULL     | 08:20:00 |
| 31 | J8      | STD     |       2 | 08:49:00 | 08:50:00 |
| 32 | J8      | STA     |       3 | 09:35:00 | NULL     |
+----+---------+---------+---------+----------+----------+

Есть четыре рейса (от J1 до J8) в каждом направлении.

Мне нужно уметь отвечать на такие вопросы, как:

Какие рейсы идут от STB до STD (ответьте J1 и J3), но исключают поездки в обратном направлении (J5 и J7)?

Какие маршруты останавливаются в STC (ответьте J1, J2, J5 и J6)?

Я пробовал подзапросы, самосоединение, создание временной таблицы и последующий запрос, но я бился головой о кирпичную стену. Я даже не знаю, оптимален ли дизайн моих столов.

Наконец, вот SQL для создания и заполнения таблиц:

CREATE TABLE `journeys` (
  `id` int(11) NOT NULL,
  `j_id` int(11) NOT NULL,
  `journey` varchar(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `journeys` (`id`, `j_id`, `journey`) VALUES
(1, 1, 'J1'),
(2, 2, 'J2'),
(3, 3, 'J3'),
(4, 4, 'J4'),
(5, 5, 'J5'),
(6, 6, 'J6'),
(7, 7, 'J7'),
(8, 8, 'J8');

.

CREATE TABLE `stations` (
  `id` int(11) NOT NULL,
  `s_id` int(11) NOT NULL,
  `station` varchar(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `stations` (`id`, `s_id`, `station`) VALUES
(1, 1, 'STA'),
(2, 2, 'STB'),
(3, 3, 'STC'),
(4, 4, 'STD'),
(5, 5, 'STE'),
(6, 6, 'STF');

.

CREATE TABLE `schedules` (
  `id` int(11) NOT NULL,
  `j_id` int(11) NOT NULL,
  `s_id` int(11) NOT NULL,
  `s_order` int(11) NOT NULL,
  `a_time` time DEFAULT NULL,
  `d_time` time DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `schedules` (`id`, `j_id`, `s_id`, `s_order`, `a_time`, `d_time`) 
VALUES
(1, 1, 1, 1, NULL, '07:00:00'),
(2, 1, 2, 2, '07:09:00', '07:10:00'),
(3, 1, 3, 3, '07:29:00', '07:30:00'),
(4, 1, 4, 4, '07:44:00', '07:45:00'),
(5, 1, 5, 5, '07:59:00', '08:00:00'),
(6, 1, 6, 6, '08:15:00', NULL),
(7, 2, 1, 1, NULL, '07:30:00'),
(8, 2, 3, 2, '07:59:00', '08:00:00'),
(9, 2, 6, 3, '08:45:00', NULL),
(10, 3, 1, 1, NULL, '08:00:00'),
(11, 3, 2, 2, '08:09:00', '08:10:00'),
(12, 3, 4, 3, '08:44:00', '08:45:00'),
(13, 3, 5, 4, '09:00:00', NULL),
(14, 4, 1, 1, NULL, '08:30:00'),
(15, 4, 4, 2, '09:14:00', '09:15:00'),
(16, 4, 6, 3, '09:45:00', NULL),
(17, 5, 6, 1, NULL, '07:10:00'),
(18, 5, 5, 2, '07:24:00', '07:25:00'),
(19, 5, 4, 3, '07:39:00', '07:40:00'),
(20, 5, 3, 4, '07:54:00', '07:55:00'),
(21, 5, 2, 5, '08:14:00', '08:15:00'),
(22, 5, 1, 6, '08:25:00', NULL),
(23, 6, 6, 1, NULL, '07:30:00'),
(24, 6, 3, 2, '08:14:00', '08:15:00'),
(25, 6, 1, 3, '08:45:00', NULL),
(26, 7, 5, 1, NULL, '08:00:00'),
(27, 7, 4, 2, '08:14:00', '08:15:00'),
(28, 7, 2, 3, '08:49:00', '08:50:00'),
(29, 7, 1, 4, '09:00:00', NULL),
(30, 8, 6, 1, NULL, '08:20:00'),
(31, 8, 4, 2, '08:49:00', '08:50:00'),
(32, 8, 1, 3, '09:35:00', NULL);

Надеюсь, я предоставил достаточно информации, чтобы кто-то мог помочь.

Спасибо за ваше время и терпение.

*****ОБНОВИТЬ*****

Хорошо, я добился некоторого прогресса. Запустите следующий SQL в отдельных сегментах (это приведет к ошибке, если я запущу все вместе):

DROP TEMPORARY TABLE IF EXISTS tableSTART;

.

DROP TEMPORARY TABLE IF EXISTS tableEND;

.

CREATE TEMPORARY TABLE IF NOT EXISTS tableSTART AS
(
SELECT schedules.id, journeys.journey, stations.station, schedules.s_order, 
schedules.a_time, schedules.d_time
FROM `schedules` 
    JOIN `journeys` ON schedules.j_id = journeys.j_id
    JOIN `stations` ON schedules.s_id = stations.s_id
WHERE stations.station = "STB"
);

.

CREATE TEMPORARY TABLE IF NOT EXISTS tableEND AS
(
SELECT schedules.id, journeys.journey, stations.station, schedules.s_order, 
schedules.a_time, schedules.d_time
FROM `schedules` 
    JOIN `journeys` ON schedules.j_id = journeys.j_id
    JOIN `stations` ON schedules.s_id = stations.s_id
WHERE stations.station = "STE"
);

.

select ts.journey, ts.station startStn, ts.s_order startOrder, te.journey, 
te.station endStn, te.s_order endOrder
from tableSTART ts
cross join tableEND te

Здесь показаны пути следования, начальная (STB) и конечная (STE) станции, а также порядок станций начальной и конечной станций. Как мне отфильтровать это, чтобы оставить только те поездки, где начальный порядок меньше конечного (т. Е. От STB до STE и исключая поездки от STE до STB?

Заранее спасибо.


mysql schedule transport
person nicodemus    schedule 29.01.2018    source источник
comment
покажите нам запросы, которые вы пробовали   -  person Kasia Gogolek    schedule 29.01.2018
comment
В этом случае слишком много информации и слишком мало SQL. Если вы запрашиваете запросы для всех этих сценариев, то ваш вопрос слишком широкий.   -  person Tim Biegeleisen    schedule 29.01.2018
comment
Раньше у меня были все данные в одной таблице и я использовал: SELECT * FROM timetable WHERE j_id IN (SELECT j_id from timetable WHERE j_id IN (SELECT j_id FROM timetable where start_station = STB) AND next_station = STE) ORDER BY j_id ASC, start_station ASC ; Мои исследования заставили меня разделить его и использовать объединения. Я не знаю, как делать подзапросы с объединениями.   -  person nicodemus    schedule 29.01.2018
comment
Тим - Думаю, первый реальный вопрос: оптимален ли дизайн? Если нет, как мне это изменить?   -  person nicodemus    schedule 29.01.2018

Ответы (1)


arrow_upward
0
arrow_downward

Какие маршруты идут с STB на STD?

Мы хотим путешествия, которые:

  1. есть запланированная остановка на STB
  2. запланировать какую-то (другую) остановку в STD
  3. остановиться на STB перед STD
select j.journey
  from journeys j
       -- have some scheduled stop at STB
       join schedules sc1 on sc1.j_id = j.id
       join stations  st1 on st1.id = sc1.s_id
                         and st1.station = 'STB'
       -- have some (other) stop scheduled at STD
       join schedules sc2 on sc2.j_id = j.id
       join stations  st2 on st2.id = sc2.s_id
                         and st2.station = 'STD'
 -- stop at STB before STD
 where sc1.s_order < sc2.s_order;

Обратите внимание, вы также можете написать:

select j.journey
  from journeys j
       join schedules sc1 on sc1.j_id = j.id
       join stations st1  on st1.id = sc1.s_id
       join schedules sc2 on sc2.j_id = j.id
       join stations st2  on st2.id = sc2.s_id
 where st1.station = 'STB'
   and st2.station = 'STD'
   and sc1.s_order < sc2.s_order;

(установка условий в on внутренних соединений аналогична помещению их в where)

Какие маршруты останавливаются на станции ЮТК?

(вероятно, тривиально, после вышеизложенного)

select j.journey
  from journeys j
       join schedules sc on sc.j_id = j.id
       join stations  st on st.id = sc.s_id
 where st.station = 'STC';

Обратите внимание, что это именно ваш запрос с меньшим количеством столбцов и where :)

person giorgiga    schedule 01.02.2018