mysql - 匹配字段数(列)

只是出于好奇，有没有方法可以得到两个或多个表中匹配数据的计数。。如果我们有下面三张桌子，

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-
ip  |isp    |infection  |Match Count
------------------------------------
1   |aaaa   |malware    |2
1   |aaaa   |ddos       |1
2   |bbbb   |malware    |1
2   |bbbb   |other      |1
3   |cccc   |ddos       |2
3   |cccc   |trojan     |1
4   |dddd   |ddos       |1
4   |dddd   |trojan     |1
5   |eeee   |trojan     |1
6   |ffff   |other      |1
6   |ffff   |           |1

谢谢：）

最佳答案

您需要为此使用UNION ALL（不只是UNION）以允许重复

SELECT ip, isp, infection, COUNT(*)
FROM
(
SELECT ip, isp, infection FROM tbl_1
UNION ALL
SELECT ip, isp, infection FROM tbl_2
UNION ALL
SELECT  ip, isp, infection FROM tbl_3
) x
GROUP BY ip, isp, infection

SQLFiddle Demo

关于mysql - 匹配字段数(列)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13083650/