查询使用联接返回错误的行，即使where子句拒绝该行

Question

我正在尝试计数行，但问题是它返回的行是我的where子句中所描述的不包括的行。

该查询应当返回在CCD_ 1表中具有至少一行的网络的数目，并且CCD_；入口；和status_code为0，但由于某种原因，它也返回具有不同类型的行。

SELECT DISTINCT n.id as test_count 
FROM networks as n
INNER JOIN network_sessions AS ns ON ns.network_id = n.id
LEFT JOIN network_logs AS nl ON nl.network_session_id = ns.id AND nl.type = "ENTRANCE" AND nl.status_code = 0
WHERE n.status_time BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 00:00:59' AND nl.created_at
BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 00:00:59'
GROUP BY n.id
HAVING COUNT(nl.id) >= 1;

我做错了什么？

Answer 1

我会查看where子句，以便在AND 上使用正确的优先级

SELECT DISTINCT n.id as test_count 
FROM networks as n
INNER JOIN network_sessions AS ns ON ns.network_id = n.id
LEFT JOIN network_logs AS nl ON nl.network_session_id = ns.id AND nl.type = "ENTRANCE" AND nl.status_code = 0
WHERE 
n.status_time BETWEEN ('2020-07-01 00:00:00' AND '2020-07-31 00:00:59') 
AND 
nl.created_at BETWEEN ('2020-07-01 00:00:00' AND '2020-07-31 00:00:59')
GROUP BY n.id
HAVING COUNT(nl.id) >= 1;

在没有样本数据和预期结果的情况下，这是未经测试的。这可能是你必须审查外部并成为OR imho，但如果没有样本数据，就不可能理解

Answer 2

尝试下面的

SELECT DISTINCT n.id as test_count 
FROM networks as n
INNER JOIN network_sessions AS ns ON ns.network_id = n.id
JOIN network_logs AS nl ON nl.network_session_id = ns.id 
WHERE n.status_time BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 00:00:59' 
AND nl.created_at BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 00:00:59'
AND nl.type = "ENTRANCE" AND nl.status_code = 0

因为network_log至少包含1个值，所以您可以应用内部联接，而不需要count((

Answer 3

此查询应返回在network_log表中至少有一行的网络数，且network_log行的类型必须为"入口；状态代码为0的

这些条件在LEFT JOIN的ON子句中。如果您真的想通过它们进行筛选，那么使用INNER JOIN——和/或将条件移动到WHERE子句中。

我还怀疑你希望所有的争吵都发生在七月，而不仅仅是最后一天的第一分钟。因此：

SELECT DISTINCT n.id as test_count 
FROM networks n JOIN
network_sessions ns
ON ns.network_id = n.id JOIN
network_logs nl
ON nl.network_session_id = ns.id
WHERE nl.type = 'ENTRANCE' AND nl.status_code = 0 AND
n.status_time >= '2020-07-01' AND 
n.status_time < '2020-08-01' AND 
nl.created_at >= '2020-07-01' AND
nl.created_at < '2020-08-01' ;