我有两张桌子，如下图
location_distance
----------------------------------------------
id | fromLocid | toLocid | distance
----------------------------------------------
1 | 3 | 5 | 70
2 | 6 | 8 | 15
3 | 2 | 4 | 63
...other_table
--------------------------------------------
Id | fromLocid | toLocid | otherdata
--------------------------------------------
12 | 5 | 3 | xxxx
22 | 2 | 4 | xxxx
56 | 8 | 6 | xxxx
78 | 3 | 5 | xxxx我想检索每行的other_table中位置的距离。这是我试过的
SELECT ot.*, ld.distance FROM other_table AS ot
INNER JOIN location_distance ld ON ld.fromLocid = ot.fromLocid AND ld.toLocid = ot.toLocid如果位置值反过来，这不会返回行。我怎样才能重写上述查询产生预期的结果？我应该在联合条款中引入OR条件吗？像下面一样？
SELECT ot.*, ld.distance FROM other_table AS ot
INNER JOIN location_distance ld ON (ld.fromLocid = ot.fromLocid OR ld.fromLocid = ot.toLocid) AND (ld.toLocid = ot.fromLocid OR ld.toLocid = ot.fromLocid)但这个查询解释说：“范围检查每个记录”。 ..这是一个不好的做法？
结果
--------------------------------------------------------
Id | fromLocid | toLocid | otherdata | distance
--------------------------------------------------------
22 | 2 | 4 | xxxx | 63
78 | 3 | 5 | xxxx | 70预期结果应该是
-----------------------------------------------------
Id | fromLocid | toLocid | otherdata | distance
-----------------------------------------------------
12 | 5 | 3 | xxxx | 70
22 | 2 | 4 | xxxx | 63
56 | 8 | 6 | xxxx | 15
78 | 3 | 5 | xxxx | 70
1208
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
