SQL COUNT查询条件不满足时应返回0却返回无数据的解决问题

问题根因 #

你当前的查询存在两个核心问题：

1. LEFT JOIN后将A.current_state != 0放在WHERE子句中过滤：LEFT JOIN无匹配时A表字段全部为NULL，该条件会直接过滤掉所有无匹配的行，等价于把LEFT JOIN转为了内连接，自然不会返回计数为0的条目。
2. 按A表的timestamp分组，无匹配的行timestamp为NULL，不会被纳入分组结果。

解决方法 #

你需要先拿到所有存在的时间戳维度，再关联统计符合条件的主机数量，参考SQL如下：

SELECT 
  t.timestamp,
  COUNT(A.host) AS count
FROM 
  -- 提取所有存在的时间戳，保证每个时间戳都出现在结果中
  (SELECT DISTINCT timestamp FROM tableA) t
CROSS JOIN 
  -- 提前过滤出位于Barcelona的所有主机
  (SELECT host FROM tableB WHERE location = 'Barcelona') b
LEFT JOIN tableA A 
  ON A.timestamp = t.timestamp 
  AND A.host = b.host 
  -- A表的过滤条件放在JOIN关联条件中，不满足的仅关联结果为NULL，不会被过滤
  AND A.current_state != 0
GROUP BY t.timestamp
ORDER BY t.timestamp DESC;

逻辑说明 #

• 先通过去重查询tableA的timestamp，确保所有需要返回的时间戳都被保留
• CROSS JOIN得到每个时间戳对应的待统计主机全集，避免维度缺失
• LEFT JOIN的关联条件中加入状态过滤，不符合条件的关联结果返回NULL，不会被全局过滤
• COUNT统计A.host时NULL值不会被计数，无匹配时自然返回0

内容的提问来源于stack exchange，提问作者pedro_lopz