mysql - 对多个表使用 SUM 会产生意想不到的结果

Question

我对数据库查询没有真正的经验，而且似乎找不到我要找的东西。

我有一个简单的数据库，有 2 个表、日志和 block 。

Log:
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(11)          | NO   | PRI | NULL    | auto_increment | 
| srcip    | varchar(25)      | NO   |     | NULL    |                | 
| dstip    | varchar(25)      | NO   |     | NULL    |                | 
| cnt      | int(10) unsigned | NO   |     | NULL    |                | 
+----------+------------------+------+-----+---------+----------------+

Block: 
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ip    | varchar(25) | YES  |     | NULL    |       | 
| shun  | int(11)     | NO   |     | NULL    |       | 
| count | int(11)     | NO   |     | 0       |       | 
+-------+-------------+------+-----+---------+-------+

我的问题是这样的。运行以下查询时，sum(cnt) 给出意外结果。

SELECT DISTINCT srcip, SUM(cnt) as hitcnt, COUNT(DISTINCT dstip) as dstcnt
FROM  block,log
WHERE (dstip LIKE '10.10.10.%'
AND block.ip != srcip
AND srcip NOT LIKE  '$restricted'
AND time >= DATE_SUB(NOW(), INTERVAL $start_units $units)
AND time <= DATE_SUB(NOW(), INTERVAL $end_units $units))
GROUP BY srcip
ORDER BY hitcnt, INET_ATON(srcip) asc;"

如果我运行相同的查询并删除日志表和引用它的语句，则每个 IP 地址的总和是正确的。使用这两个表的原因是我只想显示未被阻止的 IP 地址。阻止的 IP 信息包含在阻止表中。我已经弄清楚如何使用两个表仅显示被阻止的 IP 地址，但显示相反的 IP 地址似乎让我困惑。

我还有几个查询需要构建，我想如果我能让这个查询正常工作，其余的应该就位了。

如有任何帮助，我们将不胜感激。谢谢。

更新:除非 SUM 仍处于关闭状态，否则此方法有效。其他一切看起来都不错。请注意，我删除了一两行以使其易于测试。

SELECT DISTINCT srcip, SUM(cnt) as hitcnt, COUNT(DISTINCT dstip) as dstcnt
FROM  log a JOIN block b
ON a.srcip != b.ip
WHERE (dstip LIKE '10.10.10.%'
AND time >= DATE_SUB(NOW(), INTERVAL 1 hour)
AND time <= DATE_SUB(NOW(), INTERVAL 0 hour)
AND srcip != ip)
GROUP BY srcip
HAVING hitcnt >= '2' AND dstcnt >= '2'
ORDER BY hitcnt, INET_ATON(srcip) ASC;

我明白了!!我知道你们都会引导我走向正确的方向。谢谢大家的帮助。

SELECT DISTINCT srcip, SUM(cnt) as hitcnt, COUNT(DISTINCT dstip) as dstcnt
FROM  log a LEFT JOIN block b
ON srcip = ip
WHERE (dstip LIKE '10.10.10.%' 
AND time >= DATE_SUB(NOW(), INTERVAL $start_units $units)
AND time <= DATE_SUB(NOW(), INTERVAL $end_units $units))
GROUP BY srcip
HAVING hitcnt >= '2' AND dstcnt >= '2'
ORDER BY hitcnt, INET_ATON(srcip) ASC;

Answer 1

正如 xQbert 提到的，您可以通过 FROM block, log 进行交叉连接，这会为您提供 log 中的每一行，就像 block 中的行一样频繁。然后您过滤掉其中一些，但仍然比以前有更多的行。

解决方案是子选择。

SELECT DISTINCT log.srcip, 
    (SELECT SUM(cnt) FROM log WHERE log.srcip = srcip) as hitcnt, COUNT(DISTINCT dstip) as dstcnt
FROM  block,log
WHERE (dstip LIKE '10.10.10.%'
AND block.ip != srcip
AND srcip NOT LIKE  '$restricted'
AND time >= DATE_SUB(NOW(), INTERVAL $start_units $units)
AND time <= DATE_SUB(NOW(), INTERVAL $end_units $units))
GROUP BY srcip
ORDER BY hitcnt, INET_ATON(srcip) ASC

或者您可以使用子选择来创建第三个表来连接:

SELECT DISTINCT log.srcip, hitcnt, COUNT(DISTINCT dstip) as dstcnt
FROM  block,log,(SELECT srcip, SUM(cnt) as hitcnt FROM log GROUP BY srcip) t
WHERE (dstip LIKE '10.10.10.%'
AND block.ip != log.srcip
AND log.srcip = t.srcip
AND log.srcip NOT LIKE  '$restricted'
AND time >= DATE_SUB(NOW(), INTERVAL $start_units $units)
AND time <= DATE_SUB(NOW(), INTERVAL $end_units $units))
GROUP BY log.srcip
ORDER BY hitcnt, INET_ATON(srcip) ASC