MySQL查询需求：按站点order升序筛选匹配起止地点的线路

嘿，我来帮你解决这个线路筛选的问题！你的需求很明确：从一对多关联的Route和Stop表中，找出同时包含两个指定loc_id站点，且其中一个站点的order值小于另一个的Route，对吧？

先说说你原来的查询为啥失效 #

你之前写的查询没有关联到具体的Route——子查询(SELECT stop.order FROM stop WHERE stop.locationId = 1)会返回所有loc_id=1的Stop的order值，如果存在多条记录，直接用<比较会报错；更关键的是，它完全没考虑每条Route自己独立的站点顺序，根本没法对应到具体的线路上。

正确的查询方案 #

我们可以通过自连接Stop表的方式来实现，逻辑清晰且高效：

带参数的查询语句 #

假设你要传入的两个loc_id分别是起点@start_loc和终点@end_loc，替换成具体数值（比如1和2）即可：

SELECT DISTINCT r.*
FROM route r
JOIN stop s1 ON r.id = s1.route_id AND s1.loc_id = @start_loc
JOIN stop s2 ON r.id = s2.route_id AND s2.loc_id = @end_loc
WHERE s1.`order` < s2.`order`;

逻辑拆解 #

• 两次JOIN操作：第一次把Route和对应起点站点s1关联，第二次和对应终点站点s2关联，确保筛选出的Route同时包含这两个站点
• WHERE s1.order < s2.order``：精准判断当前Route内，起点站点的顺序确实在终点站点之前
• DISTINCT：避免因关联逻辑返回重复的Route记录（如果你的表结构保证每个Route+loc_id唯一，也可以省略，但加上更稳妥）

用示例数据测试验证 #

用你提供的示例数据测试：

1. 当输入@start_loc=1、@end_loc=2时：
   • s1对应Route1的loc_id=1站点，order=1；s2对应Route1的loc_id=2站点，order=2
   • 满足1<2，返回Route 1
2. 当输入@start_loc=2、@end_loc=1时：
   • s1对应Route2的loc_id=2站点，order=2；s2对应Route2的loc_id=1站点，order=3
   • 满足2<3，返回Route 2

完全符合你的预期场景！

如果是在应用程序中调用，你可以把@start_loc和@end_loc换成对应语言的参数占位符（比如?或:start_loc），避免SQL注入风险。

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