MySQL 2 个字段上的 SQL LEFT-JOIN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18914698/
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
SQL LEFT-JOIN on 2 fields for MySQL
提问by Possa
I have a view Aand a view B.
我有一个观点A和一个观点B。
In AI have a lot of information about some systems, like IPand portwhich I want to preserve all. In BI have just one information that I want to add at A.
在A我有很多的信息的一些系统，如IP和port我要保存所有。在B我只有一个信息要添加到A.
The matching fields between the two views are IPand Port. So I have to match those hosts which has the same IP and Port in both views.
两个视图之间的匹配字段是IP和Port。所以我必须匹配那些在两个视图中具有相同 IP 和端口的主机。
Examples:
例子：
View A:
视图 A：
IP | OS | Hostname | Port | Protocol
1 | Win | hostONE | 80 | tcp
1 | Win | hostONE | 443 | tcp
1 | Win | hostONE | 8080 | tcp
2 | Linux | hostTWO | 21 | tcp
2 | Linux | hostTWO | 80 | tcp
3 | Linux | hostTR | 22 | tcp
View B:
视图 B：
IP | Port | State
1 | 443 | Open
2 | 80 | Closed
OUTPUT
输出
IP | OS | Hostname | Port | Protocol | State
1 | Win | hostONE | 80 | tcp |
1 | Win | hostONE | 443 | tcp | Open
1 | Win | hostONE | 8080 | tcp |
2 | Linux | hostTWO | 21 | tcp | Closed
2 | Linux | hostTWO | 80 | tcp |
3 | Linux | hostTR | 22 | tcp |
Note: Is possible that some hosts of the view A has no IP/Port related items in View B.
注意：有可能视图 A 的某些主机在视图 B 中没有 IP/Port 相关项。
Is also possible that some hosts of the view A has some match in the View B.
也有可能视图 A 的某些主机在视图 B 中有一些匹配。
I thought that I should be using LEFT JOIN in order to have all the entry of View A and the correct associated entry of View B, but it didn't work. I'm not able to adjust the query with the right WHERE clause and JOIN solution.
我认为我应该使用 LEFT JOIN 以获得视图 A 的所有条目和视图 B 的正确关联条目，但它没有用。我无法使用正确的 WHERE 子句和 JOIN 解决方案调整查询。
Any idea?
任何的想法？
回答by juergen d
select a.ip, a.os, a.hostname, a.port, a.protocol,
b.state
from a
left join b on a.ip = b.ip
and a.port = b.port
回答by veljasije
Let's try this way:
让我们试试这个方法：
select
a.ip,
a.os,
a.hostname,
a.port,
a.protocol,
b.state
from a
left join b
on a.ip = b.ip
and a.port = b.port /*if you has to filter by columns from right table , then add this condition in ON clause*/
where a.somecolumn = somevalue /*if you have to filter by some column from left table, then add it to where condition*/
So, in whereclause you can filter result set by column from right table only on this way:
因此，在where子句中，您只能通过这种方式从右表中按列过滤结果集：
...
where b.somecolumn <> (=) null
