MySql - 使用左外连接连接查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15313724/
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 - concat in query with left outer join
提问by Bob
I don't get it. I've tried:
我不明白。我试过了：
SELECT
table1.name, CONCAT( country, '.', id ) AS table1.code,
table2.name
FROM tabel1
LEFT OUTER JOIN
table2 ON ( table1.code = table2.code )
I need to combine countryand idto country.idbecause table2.codehas this schema.
我需要结合country和idtocountry.id因为table2.code有这个模式。
Thanks in advance.
提前致谢。
回答by peterm
If I understand you correct you might need something like this
如果我理解你是正确的，你可能需要这样的东西
SELECT t1.name t1_name,
t2.name t2_name
FROM table1 t1 LEFT JOIN
table2 t2 ON CONCAT(t1.country, '.', t1.id ) = t2.code
回答by SiliconMind
For anyone pulling his hair out because of the poor performance of ON CONCAT()joins: make sure you cast your non string values to CHARit improves the performance astronomically:
对于由于ON CONCAT()连接性能不佳而拔出头发的人：确保将非字符串值转换为CHAR它可以天文数字地提高性能：
SELECT t1.name t1_name,
t2.name t2_name
FROM table1 t1 LEFT JOIN
table2 t2 ON CONCAT(t1.country, '.', CAST(t1.id AS CHAR) ) = t2.code
Explanation hides in MySQL docs for CONCAT()function:
CONCAT()函数的解释隐藏在 MySQL 文档中：
…If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast…
...如果所有参数都是非二进制字符串，则结果为非二进制字符串。如果参数包含任何二进制字符串，则结果为二进制字符串。数字参数被转换为其等效的二进制字符串形式；如果你想避免这种情况，你可以使用显式类型投...
Credit for this goes to Aurimas Mikalauskas.
