匹配列

Question

我想知道是否有一种方法可以显示一组匹配或不同的数据。比方说我有三张桌子

tbl_1-
ip  |isp    |infection
----------------------
1   |aaaa   |malware
2   |bbbb   |malware
3   |cccc   |ddos
3   |cccc   |trojan
4   |dddd   |ddos

tbl_2-
ip  |isp    |infection
----------------------
1   |aaaa   |malware
3   |cccc   |ddos
4   |dddd   |trojan
5   |eeee   |trojan
6   |ffff   |other

tbl_3-
ip  |isp    |infection
----------------------
1   |aaaa   |ddos
6   |ffff   |
2   |bbbb   |other

为了得到如下结果，

Result i need-
ip  |isp    |infection
----------------------
1   |aaaa   |malware
1   |aaaa   |ddos
2   |bbbb   |malware
2   |bbbb   |other
3   |cccc   |ddos
3   |cccc   |trojan
4   |dddd   |ddos
4   |dddd   |trojan
5   |eeee   |trojan
6   |ffff   |other
6   |ffff   |

问题是我不知道在这种情况下使用什么方法，我使用了这个查询full join，

SELECT a. * , b. * , c. *
FROM tbl_1 a
LEFT OUTER JOIN tbl_2 b
USING ( ip, isp, infection )
LEFT OUTER JOIN tbl_3 c
USING ( ip, isp, infection )
UNION
SELECT a. * , b. * , c. *
FROM tbl_2 b
LEFT OUTER JOIN tbl_1 a
USING ( ip, isp, infection )
LEFT OUTER JOIN tbl_3 c
USING ( ip, isp, infection )
UNION
SELECT a. * , b. * , c. *
FROM tbl_3 c
LEFT OUTER JOIN tbl_1 a
USING ( ip, isp, infection )
LEFT OUTER JOIN tbl_2 b
USING ( ip, isp, infection )

但这以不同的方式给出了结果，

Result i get-
ip  |isp    |infection  ip  |isp    |infection  ip  |isp    |infection
----------------------  ----------------------  ----------------------
1   |aaaa   |malware    1   |aaaa   |malware    NULL|NULL   |NULL
NULL|NULL   |NULL       NULL|NULL   |NULL       1   |aaaa   |ddos   
2   |bbbb   |malware    NULL|NULL   |NULL       NULL|NULL   |NULL   
NULL|NULL   |NULL       NULL|NULL   |NULL       2   |bbbb   |other
3   |cccc   |ddos       3   |cccc   |ddos       NULL|NULL   |NULL
3   |cccc   |trojan     NULL|NULL   |NULL       NULL|NULL   |NULL
4   |dddd   |ddos       NULL|NULL   |NULL       NULL|NULL   |NULL
NULL|NULL   |NULL       4   |dddd   |trojan     NULL|NULL   |NULL
NULL|NULL   |NULL       5   |eeee   |trojan     NULL|NULL   |NULL       
NULL|NULL   |NULL       6   |ffff   |other      NULL|NULL   |NULL       
NULL|NULL   |NULL       NULL|NULL   |NULL       6   |ffff   |

请在这一点上帮助我，我只想在3列中显示数据。

非常感谢:)

Answer 1

这是一个简单的UNION

SELECT ip, isp, infection FROM tbl_1
UNION
SELECT ip, isp, infection FROM tbl_2
UNION
SELECT  ip, isp, infection FROM tbl_3
ORDER BY IP

SQLFiddle Demo

Answer 2

SELECT * 
FROM   (
        SELECT * FROM tbl_1 
        UNION 
        SELECT * FROM tbl_2 
        UNION 
        SELECT * FROM tbl_3
       ) AS t
ORDER BY ip, isp

Answer 3

您应该对这三个表执行UNION操作，然后执行GROUP BY以删除重复项：

SELECT ip, isp, infection 
FROM tbl_1

UNION

SELECT ip, isp, infection 
FROM tbl_2

UNION

SELECT  ip, isp, infection 
FROM tbl_3

GROUP BY ip, isp, infection

ORDER BY IP