MySQL交通时刻表数据库查询与表结构优化技术问询

嗨，我看你已经在交通时刻表数据库上折腾了一阵子了，先帮你搞定当前临时表的过滤问题，然后再把那两个核心查询需求解决掉，顺便聊聊怎么优化你的表结构~

一、解决临时表的行程过滤问题 #

你当前用cross join会把所有起始站和终点站的组合都列出来，这显然不是你要的结果。我们需要关联同一个行程，再判断起始站的停靠顺序小于终点站的顺序，就能精准筛选出正向行程了。修改后的SQL如下：

DROP TEMPORARY TABLE IF EXISTS tableSTART;
DROP TEMPORARY TABLE IF EXISTS tableEND;

CREATE TEMPORARY TABLE IF NOT EXISTS tableSTART AS (
 SELECT journeys.journey, stations.station, schedules.s_order 
 FROM `schedules` 
 JOIN `journeys` ON schedules.j_id = journeys.j_id 
 JOIN `stations` ON schedules.s_id = stations.s_id 
 WHERE stations.station = "STB"
);

CREATE TEMPORARY TABLE IF NOT EXISTS tableEND AS (
 SELECT journeys.journey, stations.station, schedules.s_order 
 FROM `schedules` 
 JOIN `journeys` ON schedules.j_id = journeys.j_id 
 JOIN `stations` ON schedules.s_id = stations.s_id 
 WHERE stations.station = "STE"
);

-- 关联同一行程并过滤正向顺序
select ts.journey, 
       ts.station as startStn, 
       ts.s_order as startOrder, 
       te.station as endStn, 
       te.s_order as endOrder 
from tableSTART ts 
inner join tableEND te on ts.journey = te.journey
where ts.s_order < te.s_order;

其实这个需求完全可以不用临时表，用单查询就能实现，后面会详细说明~

二、核心查询需求的实现 #

1. 筛选从STB到STD的正向行程（预期J1、J3） #

推荐两种高效实现方式，按需选择：

方式一：自连接实现

SELECT DISTINCT j.journey
FROM journeys j
-- 关联起始站STB的记录
JOIN schedules s_stb ON j.j_id = s_stb.j_id
JOIN stations stb ON s_stb.s_id = stb.s_id AND stb.station = 'STB'
-- 关联终点站STD的记录
JOIN schedules s_std ON j.j_id = s_std.j_id
JOIN stations std ON s_std.s_id = std.s_id AND std.station = 'STD'
-- 确保STB的停靠顺序在STD之前（正向行程）
WHERE s_stb.stop_order < s_std.stop_order;

方式二：EXISTS子查询实现

SELECT DISTINCT j.journey
FROM journeys j
JOIN schedules s_stb ON j.j_id = s_stb.j_id
JOIN stations stb ON s_stb.s_id = stb.s_id AND stb.station = 'STB'
WHERE EXISTS (
    SELECT 1
    FROM schedules s_std
    JOIN stations std ON s_std.s_id = std.s_id
    WHERE s_std.j_id = j.j_id
      AND std.station = 'STD'
      AND s_std.stop_order > s_stb.stop_order
);

2. 查询经停STC的行程（预期J1、J2、J5、J6） #

这个需求很直接，只要找到包含STC站点的行程即可，用DISTINCT去重避免重复结果：

SELECT DISTINCT j.journey
FROM journeys j
JOIN schedules s ON j.j_id = s.j_id
JOIN stations st ON s.s_id = st.s_id
WHERE st.station = 'STC';

三、表结构优化建议 #

当前表结构可用，但可以做一些优化来提升数据完整性和查询效率：

• 给业务唯一标识符加唯一约束：journey表的journey字段（J1-J8）、station表的station字段（STB、STD等）都是业务上的唯一值，建议添加UNIQUE约束，避免重复数据，同时查询时可以直接用这些业务字段关联，可读性更强。
• 添加外键约束：schedule表的s_id和j_id应该分别关联station和journey的主键，保证数据的引用完整性，避免出现不存在的站点或行程ID。
• 优化索引：给schedule表建立联合索引(j_id, stop_order)，因为很多查询都是按行程+停靠顺序筛选的，这个索引能大幅提升这类查询的速度。
• 字段名优化：把s_order改成stop_order（你之前的描述里提到了stop_order，应该是笔误），a_time改成arrival_time，d_time改成departure_time，这样字段含义更清晰，后续维护更方便。

内容的提问来源于stack exchange，提问作者nicodemus