mysql - 用一些优先级或其他可能的方法来连接表来解决

Question

我有两张 table

路由表

Route_id From_id   To_id
       1 City1  City5
       2 City5  City1
       3 City5  City4
       4 City1  City3 

Via_table内容

via_id, route_id,  City_id, Priority 
     1         1   city2    1
     2         1   city3    2
     3         1   city4    3
     4         2   city4    1
     5         2   city3    2
     6         2   city2    3
     7         4   city2    1

我正在查询以获取特定搜索的路线例如 city1_id 到 city3_id

select route_id from route_Table as a 
left_join via_Table as b on a.route_id=b.route_id 
left join via_Table as b1 on a.route_id=b1.route_id 
where (a.from_id=city1_id And a.to_id=city2_id)
or (a.from_id=city1_id And b.city_id=city2_id)
or (b.city_id=city1_id AND b1.city_id=city2_id) 
or (b.city_id=city1_id AND a.to_id=city2_id);

在这里，我获得了每个查询的正确详细信息，例如我要去 city2 到 city4获取路线ID

1,4

城市1到城市5

1

但问题是 city2 到 city4 并不是必需的

4

因为它返回路径。如何解决这个问题我认为问题可以通过路线 id 或路线优先级解决但我不知道要解决这个问题提前致谢...

Answer 1

您应该添加一个条件，以便 b 记录始终位于 b1 记录之前就优先级而言。另外，为了避免重复，请添加一个distinct:

select      distinct a.route_id
from        route_Table as a 
left join   via_Table as b 
        on  a.route_id = b.route_id 
left join   via_Table as b1
        on  a.route_id = b1.route_id
        and b.priority < b1.priority
where       (a.from_id = :city1_id and a.to_id    = :city2_id)
        or  (a.from_id = :city1_id and b.city_id  = :city2_id)
        or  (b.city_id = :city1_id and b1.city_id = :city2_id) 
        or  (b.city_id = :city1_id and a.to_id    = :city2_id);

请注意，当数据量很大时，查询可能会变慢。如果您还存储以下两个端点，事情会简化很多via_table 中优先级分别为 0 和 9999 的路由。如果没有，您可以考虑创建一个模拟该 View 的 View :

create or replace view path as
select      route_id,
            city_id,
            priority
from        via_table
union
select      route_id,
            from_id,
            0
from        route_table
union
select      route_id,
            to_id,
            9999
from        route_table;    

那么你的查询就变成了:

select      b.route_id
from        path as b 
inner join  path as b1
        on  b.route_id = b1.route_id
        and b.priority < b1.priority
where       b.city_id  = :city1_id
        and b1.city_id = :city2_id;

我已经在这个 SQL Fiddle 中应用了它