嵌套查询以获取两个条件的计数

我有这3张桌子，

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  |ipCount    |ispCount   |infectionCount
--------------------------------------------------------------
1   |aaaa   |malware    |3          |3          |2
1   |aaaa   |ddos       |3          |3          |1
2   |bbbb   |other      |2          |2          |1
2   |bbbb   |malware    |2          |2          |1
3   |cccc   |ddos       |3          |3          |2
3   |cccc   |trojan     |3          |3          |1
4   |dddd   |ddos       |2          |2          |1
4   |dddd   |trojan     |2          |2          |1
5   |eeee   |trojan     |1          |1          |1
6   |ffff   |other      |2          |2          |1
6   |ffff   |           |2          |2          |1

ipCount, ispCount -> count of matching ip and isp
    eg-there are 3 records with ip = 1 and isp = aaaa

infectionCount -> count of matching infections per ip and isp
    eg-there are 2 infections that says malware where ip = 1 and isp = aaaa

我想我需要一个嵌套查询，但我不知道如何用两个条件进行计数；你能帮我吗？

EDIT：我尝试过的代码，

SELECT ip, isp, infection, count(ip), count(isp), count(infection)
FROM (

SELECT ip, isp, infection
FROM tbl_1
UNION ALL
SELECT ip, isp, infectionType
FROM tbl_2
UNION ALL
SELECT ip, isp, infection
FROM tbl_3
)x

GROUP BY ip, isp, infection

但它没有给出我想要的结果，因为我不知道如何在一个查询中进行两种类型的计数

您需要对列进行分组infection and (ip & ipc）不同地然后使用子查询将它们连接起来，如下所示：

SELECT t1.ip, t1.isp, t2.infection, t1.ipc, t1. ispc, t2.incount
FROM
    (SELECT ip, isp, infection, COUNT(ip) as ipc, COUNT(isp) as ispc
    FROM (
       SELECT ip, isp, infection
       FROM tbl1
       UNION ALL
       SELECT ip, isp, infection
       FROM tbl2
       UNION ALL
       SELECT ip, isp, infection
       FROM tbl3
       )x
     GROUP BY ip, isp) t1
JOIN
    (SELECT ip, isp, infection, COUNT(infection) as incount
     FROM (
       SELECT ip, isp, infection
       FROM tbl1
       UNION ALL
       SELECT ip, isp, infection
       FROM tbl2
       UNION ALL
       SELECT ip, isp, infection
       FROM tbl3
       )x
    GROUP BY ip, isp,  infection)t2
ON t1.ip = t2.ip
ORDER BY ip, isp, infection Desc

请参阅此 SQLFiddle http://sqlfiddle.com/#!2/56190/5

Note:我认为您想要的输出是错误的，因为：

1. In Table3没有infection for ip=6但它在你的输出中
2. infection other您的输出中缺少（而是有malware)