多连接和计数

Question

我试图从两个或更多的表中得到多个出现字段的计数，然后从这个问题中解决了这个问题，

使用两个条件获得计数的嵌套查询

该解决方案有效，但当在以下数据结构中尝试时，

它执行，但从不显示任何结果。但是，关于前面的问题，我贴出了这种解决方案有效的fine..Can，请大家在这个issue..it上提供帮助，甚至不会说有语法错误。

编辑: sql查询

SELECT t1.timeStamp, t1.localIp, t2.localPort, t3.localGeo, t4.isp, t5.foreignIp, t6.foreignPort, t7.foreignGeo, t8.infection,t1.timeStampCount, t1.localIpCount, t2.localPortCount, t3.localGeoCount, t4.ispCount, t5.foreignIpCount, t6.foreignPortCount, t7.foreignGeoCount, t8.infectionCount
FROM
(SELECT timeStamp,localIp,localPort,localGeo,isp,foreignIp,foreignPort,foreignGeo,infection,COUNT(timeStamp) AS 'timeStampCount',COUNT(localIp) AS 'localIpCount' 
FROM (
    SELECT date_format(timestamp, '%Y-%m-%d %h') AS 'timeStamp', ip AS 'localIp', port AS 'localPort', "" AS 'localGeo', "" AS 'isp', "" AS 'foreignIp', "" AS 'foreignPort', "" AS 'foreignGeo', "" AS 'infection'  
    FROM tbl_shadowserver_bot_geo 
    UNION ALL 
    SELECT date_format(timestamp, '%Y-%m-%d %h') AS 'timeStamp', ip AS 'localIp', "" AS 'localPort', "" AS 'localGeo', "" AS 'isp', "" AS 'foreignIp', "" AS 'foreignPort', "" AS 'foreignGeo', Formatreport AS 'infection'  
    FROM tbl_www_cymru_com 
) c  
GROUP BY timeStamp,localIp)t1  
JOIN 
(SELECT timeStamp,localIp,localPort,localGeo,isp,foreignIp,foreignPort,foreignGeo,infection,COUNT(localPort) AS 'localPortCount' 
FROM (
    SELECT date_format(timestamp, '%Y-%m-%d %h') AS 'timeStamp', ip AS 'localIp', port AS 'localPort', "" AS 'localGeo', "" AS 'isp', "" AS 'foreignIp', "" AS 'foreignPort', "" AS 'foreignGeo', "" AS 'infection'  
    FROM tbl_shadowserver_bot_geo 
    UNION ALL 
    SELECT date_format(timestamp, '%Y-%m-%d %h') AS 'timeStamp', ip AS 'localIp', "" AS 'localPort', "" AS 'localGeo', "" AS 'isp', "" AS 'foreignIp', "" AS 'foreignPort', "" AS 'foreignGeo', Formatreport AS 'infection'  
    FROM tbl_www_cymru_com 
) c  
GROUP BY timeStamp,localIp,localPort)t2  
ON t1.timeStamp=t2.timeStamp 
JOIN 
(SELECT timeStamp,localIp,localPort,localGeo,isp,foreignIp,foreignPort,foreignGeo,infection,COUNT(localGeo) AS 'localGeoCount' 
FROM (
    SELECT date_format(timestamp, '%Y-%m-%d %h') AS 'timeStamp', ip AS 'localIp', port AS 'localPort', "" AS 'localGeo', "" AS 'isp', "" AS 'foreignIp', "" AS 'foreignPort', "" AS 'foreignGeo', "" AS 'infection'  
    FROM tbl_shadowserver_bot_geo 
    UNION ALL 
    SELECT date_format(timestamp, '%Y-%m-%d %h') AS 'timeStamp', ip AS 'localIp', "" AS 'localPort', "" AS 'localGeo', "" AS 'isp', "" AS 'foreignIp', "" AS 'foreignPort', "" AS 'foreignGeo', Formatreport AS 'infection'  
    FROM tbl_www_cymru_com 
) c  
GROUP BY timeStamp,localIp,localPort,localGeo)t3  
ON t1.timeStamp=t3.timeStamp 
JOIN 
(SELECT timeStamp,localIp,localPort,localGeo,isp,foreignIp,foreignPort,foreignGeo,infection,COUNT(isp) AS 'ispCount' 
FROM (
    SELECT date_format(timestamp, '%Y-%m-%d %h') AS 'timeStamp', ip AS 'localIp', port AS 'localPort', "" AS 'localGeo', "" AS 'isp', "" AS 'foreignIp', "" AS 'foreignPort', "" AS 'foreignGeo', "" AS 'infection'  
    FROM tbl_shadowserver_bot_geo 
    UNION ALL 
    SELECT date_format(timestamp, '%Y-%m-%d %h') AS 'timeStamp', ip AS 'localIp', "" AS 'localPort', "" AS 'localGeo', "" AS 'isp', "" AS 'foreignIp', "" AS 'foreignPort', "" AS 'foreignGeo', Formatreport AS 'infection'  
    FROM tbl_www_cymru_com 
) c  
GROUP BY timeStamp,localIp,localPort,localGeo,isp)t4  
ON t1.timeStamp=t4.timeStamp 
JOIN 
(SELECT timeStamp,localIp,localPort,localGeo,isp,foreignIp,foreignPort,foreignGeo,infection,COUNT(foreignIp) AS 'foreignIpCount' 
FROM (
    SELECT date_format(timestamp, '%Y-%m-%d %h') AS 'timeStamp', ip AS 'localIp', port AS 'localPort', "" AS 'localGeo', "" AS 'isp', "" AS 'foreignIp', "" AS 'foreignPort', "" AS 'foreignGeo', "" AS 'infection'  
    FROM tbl_shadowserver_bot_geo 
    UNION ALL 
    SELECT date_format(timestamp, '%Y-%m-%d %h') AS 'timeStamp', ip AS 'localIp', "" AS 'localPort', "" AS 'localGeo', "" AS 'isp', "" AS 'foreignIp', "" AS 'foreignPort', "" AS 'foreignGeo', Formatreport AS 'infection'  
    FROM tbl_www_cymru_com 
) c  
GROUP BY timeStamp,localIp,localPort,localGeo,isp,foreignIp)t5  
ON t1.timeStamp=t5.timeStamp 
JOIN 
(SELECT timeStamp,localIp,localPort,localGeo,isp,foreignIp,foreignPort,foreignGeo,infection,COUNT(foreignPort) AS 'foreignPortCount' 
FROM (
    SELECT date_format(timestamp, '%Y-%m-%d %h') AS 'timeStamp', ip AS 'localIp', port AS 'localPort', "" AS 'localGeo', "" AS 'isp', "" AS 'foreignIp', "" AS 'foreignPort', "" AS 'foreignGeo', "" AS 'infection'  
    FROM tbl_shadowserver_bot_geo 
    UNION ALL 
    SELECT date_format(timestamp, '%Y-%m-%d %h') AS 'timeStamp', ip AS 'localIp', "" AS 'localPort', "" AS 'localGeo', "" AS 'isp', "" AS 'foreignIp', "" AS 'foreignPort', "" AS 'foreignGeo', Formatreport AS 'infection'  
    FROM tbl_www_cymru_com 
) c  
GROUP BY timeStamp,localIp,localPort,localGeo,isp,foreignIp,foreignPort)t6  
ON t1.timeStamp=t6.timeStamp 
JOIN 
(SELECT timeStamp,localIp,localPort,localGeo,isp,foreignIp,foreignPort,foreignGeo,infection,COUNT(foreignGeo) AS 'foreignGeoCount' 
FROM (
    SELECT date_format(timestamp, '%Y-%m-%d %h') AS 'timeStamp', ip AS 'localIp', port AS 'localPort', "" AS 'localGeo', "" AS 'isp', "" AS 'foreignIp', "" AS 'foreignPort', "" AS 'foreignGeo', "" AS 'infection'  
    FROM tbl_shadowserver_bot_geo 
    UNION ALL 
    SELECT date_format(timestamp, '%Y-%m-%d %h') AS 'timeStamp', ip AS 'localIp', "" AS 'localPort', "" AS 'localGeo', "" AS 'isp', "" AS 'foreignIp', "" AS 'foreignPort', "" AS 'foreignGeo', Formatreport AS 'infection'  
    FROM tbl_www_cymru_com 
) c  
GROUP BY timeStamp,localIp,localPort,localGeo,isp,foreignIp,foreignPort,foreignGeo)t7  
ON t1.timeStamp=t7.timeStamp JOIN 
(SELECT timeStamp,localIp,localPort,localGeo,isp,foreignIp,foreignPort,foreignGeo,infection,COUNT(infection) AS 'infectionCount' 
FROM (
    SELECT date_format(timestamp, '%Y-%m-%d %h') AS 'timeStamp', ip AS 'localIp', port AS 'localPort', "" AS 'localGeo', "" AS 'isp', "" AS 'foreignIp', "" AS 'foreignPort', "" AS 'foreignGeo', "" AS 'infection'  
    FROM tbl_shadowserver_bot_geo 
    UNION ALL 
    SELECT date_format(timestamp, '%Y-%m-%d %h') AS 'timeStamp', ip AS 'localIp', "" AS 'localPort', "" AS 'localGeo', "" AS 'isp', "" AS 'foreignIp', "" AS 'foreignPort', "" AS 'foreignGeo', Formatreport AS 'infection'  
    FROM tbl_www_cymru_com 
    ) c  
    GROUP BY timeStamp,localIp,localPort,localGeo,isp,foreignIp,foreignPort,foreignGeo,infection)t8 
ON t1.timeStamp=t8.timeStamp ORDER BY timeStamp,localIp,localPort,localGeo,isp,foreignIp,foreignPort,foreignGeo,infection

Answer 1

首先，我将尝试用我自己的话重申你的问题，以明确这个答案的作用。对于两个表的合并中的每条记录，您都希望在结果集中有一行。该行应包含其他信息:对于给定的一组选定列，您希望从输入中计数与所有选定列中当前行相匹配的行。在每一步中，您都会添加到所选行集中。首先，用相同的时间戳计算所有行。接下来，使用相同的时间戳和相同的IP地址对所有行进行计数。诸若此类。

由于您反复从同一表的联合中选择，因此为它引入一个简短的名称可能是明智的。您可以通过VIEW来实现这一点。接下来，您希望使用该视图作为连接的第一个因素。这为每一行输入提供一行输出。对于添加的统计信息，您将加入一个子查询，该子查询对行进行计数，并按该计数的所有选定列进行分组。您希望使用所有这些选定的列将该子查询连接到查询的其余部分。例如：

CREATE VIEW view_both_tables AS
SELECT date_format(timestamp, '%Y-%m-%d %h') AS `timeStamp`, ip AS localIp, …
FROM tbl_shadowserver_bot_geo 
UNION ALL 
SELECT date_format(timestamp, '%Y-%m-%d %h') AS `timeStamp`, ip AS localIp, …
FROM tbl_www_cymru_com;

SELECT t0.*,
 t1.countSameTime,
 t2.countSameTimeAndLocalIp,
 t3.countSameTimeLocalIpAndInfection
FROM view_both_tables t0
LEFT JOIN (
  SELECT `timeStamp`,
   COUNT(*) AS `countSameTime`
  FROM view_both_tables
  GROUP BY `timeStamp`
 ) t1 USING (`timeStamp`)
LEFT JOIN (
  SELECT `timeStamp`, localIp,
   COUNT(*) AS `countSameTimeAndLocalIp`
  FROM view_both_tables
  GROUP BY `timeStamp`, localIp
 ) t2 USING (`timeStamp`, localIp)
LEFT JOIN (
  SELECT `timeStamp`, localIp, infection,
   COUNT(*) AS `countSameTimeLocalIpAndInfection`
  FROM view_both_tables
  GROUP BY `timeStamp`, localIp, infection
 ) t3 USING (`timeStamp`, localIp, infection)
;

这个查询是从我的SQL小提琴获取的。它只包含最多三个选定的列，因为在您的示例中其他列不包含任何数据，这应该清楚地说明了这一点。

通常，COUNT(*)比计算一个特定列更有效。唯一的例外是您想要计数的列可能包含应从计数中省略的NULL值，或者当您使用COUNT(DISTINCT …)时。但是，正如我对你问题的解释所指的是计数记录，而不是行，这两种例外都不适用。

作为对样式的进一步说明，使用单引号引用列名是相当混乱的SQL样式。通常，字符串使用单引号分隔。和列使用背板。您编写的是默认MySQL配置的有效语法，但仍然令人困惑。

如果性能存在问题，则可以尝试使用GROUP BY … WITH ROLLUP执行单个查询。将结果存储到临时表中，您可以从重复将该表连接到其本身中派生出所要求的结果。不确定是否会有任何性能提高，但这可能值得一试。