查询使用联接返回错误的行,即使where子句拒绝它也是如此

我会检查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;

没有样本数据和预期结果,这是未经测试的。这可能是你必须审查外部和成为一个或imho,但没有样本数据是不可能理解的

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\u logs表中至少有一行的网络数,并且网络日志行的类型必须为“ENTRANCE”,状态代码为0

ON 从句 LEFT JOIN . 如果您真的想通过它们进行筛选,那么使用 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' ;