MySQL 查询在没有可用数据时获取行数，在添加 where 子句时跳过结果

Question

即使该日期没有数据，我的以下查询也会显示数据计数。

SELECT c.date AS DATE,  COUNT(s.insertTime) AS tot_cnt   FROM (SELECT ADDDATE('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) DATE FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4 ) c LEFT  JOIN `access_log` s  ON c.date = DATE(s.insertTime) WHERE  UNIX_TIMESTAMP(c.date)  BETWEEN 1563733801 AND 1564338599 GROUP BY c.date ORDER BY c.date;

但是当我添加 userId=8 时，它会跳过所有没有任何值的日期范围

SELECT c.date AS DATE,  COUNT(s.insertTime) AS tot_cnt   FROM (SELECT ADDDATE('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) DATE FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,(SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4 ) c LEFT  JOIN `access_log` s  ON c.date = DATE(s.insertTime) WHERE  UNIX_TIMESTAMP(c.date)  BETWEEN 1563733801 AND 1564338599 AND userId=8 GROUP BY c.date ORDER BY c.date;

我需要所有日期，即使它们没有值(value)，并且当应用 userId=8 来过滤记录时也是如此。

Answer 1

应用 ON 子句中的条件，而不是 WHERE 子句:

ON c.date = DATE(s.insertTime) AND userId=8

因此LEFT JOIN 返回不匹配的行。