Mysql 内连接与 OR 条件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11702294/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql Inner join with OR condition?
提问by jane
I have 2 tables like below
我有 2 张如下表
location_distance
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
I would like to retrieve the distance b/w the locations in other_table for each row. Here's what i've tried
我想为每一行检索 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
This doesnt return the rows if the locations values are vice versa. How can i rewrite the above query to produce expected result? Should i inlude ORcondition on the join clause? like below?
如果位置值相反，这不会返回行。如何重写上述查询以产生预期结果？我应该在 join 子句中包含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)
but this query Explain says "Range checked for each record". .. is this a bad practise?
但是这个查询解释说“范围检查每条记录”。..这是一种不好的做法吗？
Result
结果
--------------------------------------------------------
Id | fromLocid | toLocid | otherdata | distance
--------------------------------------------------------
22 | 2 | 4 | xxxx | 63
78 | 3 | 5 | xxxx | 70
Expected Result should be
预期结果应该是
-----------------------------------------------------
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
回答by Taryn
You can join on the location_distancetable twice using a LEFT JOINand then use the COALESCE()function to return the correct value for the distance:
您可以location_distance使用 a 两次加入表LEFT JOIN，然后使用该COALESCE()函数返回正确的值distance：
select ot.id,
ot.fromlocid,
ot.tolocid,
ot.otherdata,
coalesce(ld1.distance, ld2.distance) distance
from other_table ot
left join location_distance ld1
on ld1.fromLocid = ot.toLocid
and ld1.toLocid = ot.fromLocid
left join location_distance ld2
on ld2.toLocid = ot.toLocid
and ld2.fromLocid = ot.fromLocid
This returns the result:
这将返回结果：
| 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 |
回答by Vatev
It will probably be faster to join the distance table twice like
两次加入距离表可能会更快
INNER JOIN location_distance ld1 ON ld1.fromLocid = ot.fromLocid AND ld1.toLocid = ot.toLocid
INNER JOIN location_distance ld2 ON ld2.toLocid = ot.fromLocid AND ld2.fromLocid = ot.toLocid
and then use an IF to determine which one to select
然后使用 IF 来确定要选择哪个
IF(ld1.fromLocid, ld1.distance, ld2.distance) as distance
