php - MySQL 中的子选择

Question

关闭。此题需要 details or clarity 。目前不接受答案。

---

想要改进这个问题吗？通过 editing this post 添加详细信息并澄清问题.

已关闭 6 年前。

Improve this question

我有下表，代表公交车及其停靠点。

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 号车站。

我可以为此编写什么查询？

Answer 1

举个例子。显然，这个解决方案不是递归的 - 但您可以继续迭代公交车路线，直到从 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 |
+--------+------------+-----------------+--------+----------------+--------+--------------+