mysql - MySql - 链表组合并存储路由数据
问题描述
我将数据存储在 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 这样的编程语言,有可能吗?
解决方案
这是您的解决方案,有任何问题吗?
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
推荐阅读
- r - 按 R 中的模式值删除列
- python - Rasa train_init.py 给出 sklearn_crfsuite pacakge 错误
- node.js - 如何忽略节点请求中的 SSL 证书验证?
- java - 查看通货膨胀始终为空
- php - php双扩展漏洞
- android - 如何在文本小部件中显示特殊字符颤动ç,〜,'
- java - 在 Spring 的存储过程中传递参数
- c++11 - 编译错误 编译 gRPC 时,`'-std=c++11' 对 C++/ObjC++ 有效,但对 C 无效
- android - KOTLIN - 如何将 TextViews 和按钮设置从 Activity 设置为片段
- python - 在 python 中实现 DAG