在MySQL的2个字段上进行SQL LEFT-JOIN [英] SQL LEFT-JOIN on 2 fields for MySQL

问题描述

我有一个视图A和一个视图B.

I have a view A and a view B.

在A中，我有很多关于某些系统的信息，例如IP和port，我想保留所有这些信息.在B中，我只想在A处添加一个信息.

In A I have a lot of information about some systems, like IP and port which I want to preserve all. In B I have just one information that I want to add at A.

两个视图之间的匹配字段是IP和Port.因此，我必须在两个视图中匹配具有相同IP和端口的那些主机.

The matching fields between the two views are IP and Port. So I have to match those hosts which has the same IP and Port in both views.

示例:

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

视图B:

IP | Port | State
1  | 443  | Open
2  | 80   | Closed

输出

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      |

注意:视图A的某些主机可能在视图B中没有与IP/端口相关的项目.

Note: Is possible that some hosts of the view A has no IP/Port related items in View B.

视图A的某些主机也可能在视图B中具有某些匹配项.

Is also possible that some hosts of the view A has some match in the View B.

我认为我应该使用LEFT JOIN来拥有View A的所有条目和View B的正确关联条目，但是它没有用. 我无法使用正确的WHERE子句和JOIN解决方案来调整查询.

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.

有什么主意吗?

推荐答案

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

这篇关于在MySQL的2个字段上进行SQL LEFT-JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！