我想创建一个数据库来存储行程的腿，其中每个腿在另一个表中都有一个fk，行程标识符将是键/唯一的。
例如：“东海岸公路旅行”：波士顿->纽约，纽约->费城，费城->巴尔的摩，巴尔的摩->华盛顿特区->罗利
稍后，我希望运行以下查询：
"Which trips contain the NYC -> Philly and Philly -> Baltimore legs?"
对于如何有效地存储这些旅行信息，我有些困惑。使用trip标识符键并将trip腿存储为纯文本列可能不是最有效的解决方案。
希望你能给我一些建议。
最佳答案
假设您的行程不是“一次性”的，而是在预定的线路上进行的（一条线路可以生成多个行程），那么您需要这样的东西：
（如果是一次性的，想象一下line就是旅行。）
注意腿的结构：
其pk包含leg_no，但不包含stop_id：leg_no确定给定行中的腿的顺序，并且还允许多个腿在必要时（例如，在往返途中）穿过同一个站。
此外，腿中只有“开始”（而不是“结束”）止点-无论“上一个”（由leg_no定义）腿决定下一个腿的开始止点。这样，你就永远无法拥有断开的腿（即，前一腿的终点站与下一腿的起点站不匹配）。
旅行的pk包含trip_no，而不是（例如）start_date_time，以允许在同一线路上同时启动多个旅行，如果您需要的话。
在您的示例“东海岸公路旅行”线上的23次旅行可以这样表示：
TRIP: LINE_ID TRIP_NO
------- -------
100 23
LINE: LINE_ID LINE_NAME
------- ---------
100 'East coast roadtrip'
LEG: LINE_ID LEG_NO STOP_ID
------- ------ -------
100 1 55
100 2 11
100 3 66
100 4 22
100 5 44
100 6 33
STOP: STOP_ID STOP_NAME
------- ---------
22 'Baltimore'
11 'NYC'
33 'Raleigh'
66 'Philly'
55 'Boston'
44 'DC'
（注意：我故意使用非序列号使连接更加清晰。）
使用此数据库结构，您可以轻松获得经过所有给定站点的行程，例如：
SELECT *
FROM TRIP
WHERE
LINE_ID IN (
SELECT LINE_ID
FROM LEG JOIN STOP ON LEG.STOP_ID = STOP.STOP_ID
WHERE STOP_NAME IN ('NYC', 'Philly', 'Baltimore')
GROUP BY LINE_ID
HAVING COUNT(DISTINCT STOP_ID) = 3
)
（注意：在旧版本的mysql上，由于查询优化器在中遇到问题，您可能希望将此查询重写为join。）
然而，如果你想得到的行程，通过这些车站的顺序，没有“间隙”之间，大腿变得毛茸茸的匆忙。可能您最好的选择是获取上面子查询的结果并在客户端对其进行分析，而不是尝试在sql中建立顺序并检测间隙（基本上是基于设置的）。