Подвыбрать в MySQL
У меня есть следующая таблица, представляющая автобусы и точки, в которых они останавливаются.
bus_table
bus stop_no station_id
1 1 1
1 2 2
1 3 3
2 1 7
2 2 8
2 3 9
3 1 3
3 2 4
3 3 5
3 4 6
3 5 7
Я хочу добраться от станции 1 до станции 9.
Какой запрос я могу написать для этого?
Решение
В качестве примера. Очевидно, что это решение не является рекурсивным, но вы можете просто продолжать итерировать по автобусным маршрутам, пока не доберетесь от A до B.
DROP TABLE IF EXISTS bus_routes;CREATE TABLE bus_routes
(bus_no INT NOT NULL
,stop_no INT NOT NULL
,station_id INT NOT NULL
,PRIMARY KEY(bus_no,stop_no)
);
INSERT INTO bus_routes VALUES
(1,1,1),
(1,2,2),
(1,3,3),
(2,1,7),
(2,2,8),
(2,3,9),
(3,1,3),
(3,2,4),
(3,3,5),
(3,4,6),
(3,5,7);
SELECT a_from.bus_no
, a_from.station_id start_from
, b_from.station_id first_change_at
, b_from.bus_no to_bus
, c_from.station_id then_change_at
, c_from.bus_no to_bus
, c_to.station_id alighting_at
FROM bus_routes a_from
JOIN bus_routes a_to
ON a_to.bus_no = a_from.bus_no
AND a_to.stop_no > a_from.stop_no
JOIN bus_routes b_from
ON b_from.station_id = a_to.station_id
JOIN bus_routes b_to
ON b_to.bus_no = b_from.bus_no
AND b_to.stop_no > b_from.stop_no
JOIN bus_routes c_from
ON c_from.station_id = b_to.station_id
JOIN bus_routes c_to
ON c_to.bus_no = c_from.bus_no
AND c_to.stop_no > c_from.stop_no
WHERE a_from.station_id = 1
AND c_to.station_id = 9;
+--------+------------+-----------------+--------+----------------+--------+--------------+
| bus_no | start_from | first_change_at | to_bus | then_change_at | to_bus | alighting_at |
+--------+------------+-----------------+--------+----------------+--------+--------------+
| 1 | 1 | 3 | 3 | 7 | 2 | 9 |
+--------+------------+-----------------+--------+----------------+--------+--------------+
Другие решения
Рекурсивный обход графика автобусных остановок (протестировано на MSSQL 2016):
DECLARE @from NCHAR(4) = 'ort1';
DECLARE @to NCHAR(4) = 'ort9';
WITH [route] ([path], [lastBus], [current])
AS
(SELECT CAST(N'' AS NVARCHAR(1000)) AS [path], CAST(N'' AS NCHAR(2)) AS [lastBus], @from AS [current]
UNION ALL
SELECT CAST( cur.[path] + N',' + busesAvailable.name + N' to ' + [next].stat AS NVARCHAR(1000)) AS [path],
busesAvailable.name AS [lastBus],
[next].stat AS [current]
FROM [route] cur
INNER JOIN dbo.BusStat busesAvailable ON busesAvailable.stat = cur.[current] AND busesAvailable.name <> cur.[lastBus]
INNER JOIN dbo.BusStat [next]
ON [next].stat <> cur.[current]
AND [next].name = busesAvailable.name)
SELECT TOP 1 [route].[path]
FROM [route]
WHERE [route].[current] = @to;
С учетом того, что таблица создана с помощью:
CREATE TABLE [dbo].[BusStat]
(
[name] NCHAR (2) NOT NULL,
[stat] NCHAR (4) NOT NULL
);
INSERT INTO [dbo].[BusStat] ([name], [stat])
SELECT 'b1', 'ort1' UNION ALL
SELECT 'b1', 'ort2' UNION ALL
SELECT 'b1', 'ort3' UNION ALL
SELECT 'b2', 'ort7' UNION ALL
SELECT 'b2', 'ort8' UNION ALL
SELECT 'b2', 'ort9' UNION ALL
SELECT 'b3', 'ort3' UNION ALL
SELECT 'b3', 'ort4' UNION ALL
SELECT 'b3', 'ort5' UNION ALL
SELECT 'b3', 'ort6' UNION ALL
SELECT 'b3', 'ort7';