Запрос MySQL для получения маршрута

Ответ 1

Вы можете решить эту проблему в хранимой процедуре. Но этот алгоритм, вероятно, будет быстрее при выполнении в памяти языка программирования. Просто убедитесь, что у вас установлен полный набор данных, поэтому вам не нужно выполнять запрос на каждую итерацию.

псевдокод:

to = 'D'
prev_to = 'A'
array = array();
while (prev_to != 'D') {
  select arrival, to into prev_arrival, prev_to
  from table 
  where departure > prev_arrival 
  and from = prev_to;

  array[] = [arrival => prev_arrival, to => prev_to]
}

return array

Изменить: я думаю, мне нечего было делать;)

Этот класс будет искать все маршруты от А до D между заданным временем начала и окончания. Также как приложение для общественного транспорта. Возможно, вы захотите использовать свои собственные методы подключения к базе данных. (Просто не используйте функции mysql_ * больше)

<?php

class RoutePlanner
{
    /** @var string */
    protected $departureTime;
    /** @var string */
    protected $departureLocation;
    /** @var string */
    protected $arrivalTime;
    /** @var string */
    protected $arrivalLocation;
    /** @var array */
    protected $schedule;
    /** @var mysqli */
    protected $db;

    /**
     * @param string $departureTime
     * @param string $departureLocation
     * @param string $arrivalTime
     * @param string $arrivalLocation
     * @throws InvalidArgumentException
     */
    public function __construct($departureTime, $departureLocation, $arrivalTime, $arrivalLocation)
    {
        $this->departureTime = $departureTime;
        $this->departureLocation = $departureLocation;
        $this->arrivalTime = $arrivalTime;
        $this->arrivalLocation = $arrivalLocation;
    }

    /**
     * @return array
     */
    public function getRoutes()
    {
        $schedule = $this->fetchSchedule();
        $routes = $this->searchRoutes($schedule);
        return $routes;
    }

    /**
     * Search all routes that start and end between given times
     * @param array $schedule - passing as parameter to ensure the order of execution
     * @return array
     */
    protected function searchRoutes(array $schedule)
    {
        $routes = array();

        foreach ($schedule as $i => $row)
        {
            if ($row['from'] == $this->departureLocation)
            {
                $routes[] = $this->getRoute($schedule, $i);
            }
        }

        return $routes;
    }

    /**
     * Get the route when starting at given point and time
     * @param $schedule
     * @param $start
     * @return array
     */
    protected function getRoute($schedule, $start)
    {
        $steps = array();

        $from = $this->departureLocation;
        $time = $this->departureTime;

        for ($i = $start; $i < count($schedule); $i++)
        {
            $row = $schedule[$i];
            if ($row['from'] == $from && $row['departure'] > $time)
            {
                $steps[] = $row;
                $from = $row['to'];
                $time = $row['arrival'];
            }
        }

        return $steps;
    }

    /**
     * @return array
     */
    protected function fetchSchedule()
    {
        if (! empty($this->schedule))
            return $this->schedule;

        $sql = "select * from schedule where departure >= ? and arrival <= ?";

        $db = $this->getDatabase();
        $statement = $db->prepare($sql);
        $statement->bind_param("ss", $this->departureTime, $this->arrivalTime);
        $statement->execute();
        $result = $statement->get_result();

        $this->schedule = $result->fetch_all(MYSQLI_ASSOC);

        $statement->close();
        $result->free();

        return $this->schedule;
    }

    /**
     * @return mysqli
     */
    protected function getDatabase()
    {
        if (empty($this->db))
            $this->db = new mysqli('localhost', 'user', 'pass', 'database');

        return $this->db;
    }

    public function __destroy()
    {
        if (! empty($this->db))
            $this->db->close();
    }
}

Используйте как:

<?php

$planner = new RoutePlanner('Amsterdam', '0300', 'Berlin', '1030');
$routes = $planner->getRoutes();

Ответ 2

Вы можете перекрестно присоединить таблицу к себе, чтобы получить каждый шаг, а затем вы можете запустить цикл на вашем PHP или что бы то ни было, проверяющее время отправления и прибытия, чтобы убедиться, что выезд после прибытия предыдущей поездки:

Итак, вам понадобится какая-то условная логика, но это основная идея SQL:

SELECT a.id,b.id,a.From, a.To, a.Departure,a.Arrival,
b.From,b.To,b.Departure,b.Arrival
FROM travel a
JOIN travel b
ON a.To = b.From
WHERE a.From = 'A'
OR b.To = 'D'
ORDER BY a.Arrival,a.Departure ASC,
b.Arrival,b.Departure ASC;

Если вы хотите сделать все это одним махом, вы можете присоединиться к таблице на себе в третий раз, а затем использовать WHERE a.From = 'A' AND c.To = 'D', однако я думаю, что было бы более эффективно просто сделать одно соединение на себе, а затем использовать условная логика в цикле и предложение WHERE для расчета поездки.

SQLFiddle

Ответ 3

это невозможно с использованием запроса MySQL. Однако вы можете достичь этого, используя свой язык программирования, такой как php, asp (web), С# (windows) и т.д.