mysql - 多重连接和计数

Question

我试图从 2 个或更多表中获取多个出现字段的计数，我从这个问题中解决了这个问题，

Nested queries to get count with two conditions

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

它执行但从不显示任何结果。但是对于上一个问题，我发布了这种解决方案，效果很好。任何人都可以帮助解决这个问题。它甚至没有说存在语法错误。

编辑: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 地址的所有行。等等。

当您从同一个表联合中重复选择时，为它引入一个短名称可能是明智的。您可以通过 creating a VIEW 这样做.接下来，您希望将该 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)
;

此查询取自 my SQL fiddle .它最多只涵盖三个选定的列，因为其他列在您的示例中不包含任何数据，而且这个想法应该很清楚。

一般来说，COUNT(*) 比计算一个特定的列更有效。唯一的异常(exception)是当您要计算的列可能包含 NULL 值时应从计数中省略，或者当您使用 COUNT(DISTINCT …) 时。但由于我对你的问题的解释是指计数记录，而不是行，所以这些异常(exception)都不适用。

关于风格的进一步说明，使用单引号来引用列名是相当困惑的 SQL 风格。通常，strings are delimited using single quotation marks , 和 columns uisng backticks .你写的是默认 MySQL 配置的有效语法，但仍然令人困惑。

如果性能有问题，您可以尝试使用 GROUP BY … WITH ROLLUP 执行单个查询。将结果存储到临时表中，您可以通过重复将该表与其自身连接来导出您要求的结果。不确定是否会有任何性能提升，但可能值得一试。