MySql-链表组合并存储路由数据

Question

我将数据作为链表解释存储在mysql数据库中。SQL 中的链表

这是数据。

+------+------------+--------------+-----------+--------------------------+--------+----------+------------+------------+
| flag | sort_order | country_name | city_name | port_name                | voyage | vessel   | route_eta  | route_etd  |
+------+------------+--------------+-----------+--------------------------+--------+----------+------------+------------+
| FROM |          0 | Indonesia    | Jakarta   | Tanjung Priok            | N010   | INFINITY | 2020-11-03 | 2020-11-04 |
| FROM |          1 | Indonesia    | Semarang  | Tanjung Emas             | N010   | INFINITY | 2020-11-04 | 2020-11-06 |
| FROM |          2 | Indonesia    | Surabaya  | Tanjung Perak            | N010   | INFINITY | 2020-11-06 | 2020-11-07 |
| TO   |          3 | Taiwan       | Taipei    | Port of Taipei (Tamshui) | N010   | INFINITY | 2020-11-13 | 2020-11-13 |
| TO   |          4 | Taiwan       | Taichung  | Port of Taichung         | N010   | INFINITY | 2020-11-14 | 2020-11-14 |
| TO   |          5 | Taiwan       | Kaohsiung | Port of Kaohsiung        | N010   | INFINITY | 2020-11-15 | 2020-11-15 |
+------+------------+--------------+-----------+--------------------------+--------+----------+------------+------------+

请注意flag列。例如，我想从印度尼西亚到台湾的一个特定港口，通过维持哪条路线刚刚过去。我需要以下组合的数据：

flag    sort_order  country_name    city_name   port_name           flag    sort_order      country_name    city_name   port_name                   via
FROM            0   Indonesia       Jakarta     Tanjung Priok       TO          3           Taiwan          Taipei      Port of Taipei (Tamshui)    Tanjung Emas, Tanjung Perak
FROM            0   Indonesia       Jakarta     Tanjung Priok       TO          4           Taiwan          Taichung    Port of Taichung            Tanjung Emas, Tanjung Perak, Port of Taipei (Tamshui)
FROM            0   Indonesia       Jakarta     Tanjung Priok       TO          5           Taiwan          Kaohsiung   Port of Kaohsiung           Tanjung Emas, Tanjung Perak, Port of Taipei (Tamshui), Port of Taichung

FROM            1   Indonesia       Semarang    Tanjung Emas        TO          3           Taiwan          Taipei      Port of Taipei (Tamshui)    Tanjung Perak
FROM            1   Indonesia       Semarang    Tanjung Emas        TO          4           Taiwan          Taichung    Port of Taichung            Tanjung Perak, Port of Taipei (Tamshui)
FROM            1   Indonesia       Semarang    Tanjung Emas        TO          5           Taiwan          Kaohsiung   Port of Kaohsiung           Tanjung Perak, Port of Taipei (Tamshui), Port of Taichung

FROM            2   Indonesia       Surabaya    Tanjung Perak       TO          3           Taiwan          Taipei      Port of Taipei (Tamshui)    DIRECT
FROM            2   Indonesia       Surabaya    Tanjung Perak       TO          4           Taiwan          Taichung    Port of Taichung            Port of Taipei (Tamshui)
FROM            2   Indonesia       Surabaya    Tanjung Perak       TO          5           Taiwan          Kaohsiung   Port of Kaohsiung           Port of Taipei (Tamshui), Port of Taichung

如果我想直接从数据库端处理这些数据，而不需要像PHP这样的编程语言，这可能吗？

Answer 1

这是您的解决方案，有问题吗？

CREATE TABLE PlaneRoutes(
unique_id int NOT NULL AUTO_INCREMENT,
route_id int NOT NULL,
flag varchar(250),
country_name varchar(250),
city_name varchar(250),
PRIMARY KEY(unique_id)
);

CREATE VIEW[PlaneRoutes_TO] AS
SELECT* FROM PlaneRoutes
WHERE flag = "TO"
CREATE VIEW[PlaneRoutes_FROM] AS
SELECT * FROM PlaneRoutes
WHERE flag = "FROM"

SELECT
a.route_id as `Route ID`,
a.country_name as `Flight from: Country`, a.city_name as `Flight from: City`,
b.country_name as `Flight to: Country`, b.city_name as `Flight to: City`
FROM[PlaneRoutes_FROM] as a
LEFT JOIN[PlaneRoutes_TO] as b
ON a.route_id = b.route_id
WHERE a.route_id = 124 AND b.route_id = 124

SS：https://prnt.sc/vbdeih