mysql - 返回0时查询不会返回结果

Question

我需要此查询在计数为 0 时返回结果，而不是只生成一个空集。我怎样才能调整我的查询，以便它生成下表，其中计数为“0”和适当的日期？

mysql> select count(test.id), date(insert_datetime) date 
from db.table test where date(insert_datetime)='2015-08-17' 
group by date(insert_datetime);
    +--------------+------------+
    | count(test.id) | date     |
    +--------------+------------+
    |           42 | 2015-08-17 |
    +--------------+------------+
    1 row in set (0.14 sec)


mysql> select count(test.id), date(insert_datetime) date 
from db.table test where date(insert_datetime)='2015-08-16' 
group by date(insert_datetime);

Empty set (0.00 sec)

Answer 1

应该这样做:

SELECT theDate AS `date`, IFNULL(subC.theCount, 0) AS `theCount`
FROM (SELECT DATE(20150817) AS `theDate`) AS subD
LEFT JOIN (
   SELECT COUNT(test.id) AS theCount, DATE(insert_datetime) AS `theDate` 
   FROM db.table AS test 
   WHERE insert_datetime BETWEEN 20150817000000 AND 20150817235959 
   GROUP BY theDate
) AS subC
USING (theDate)
;

正如另一位用户在现已删除的评论中暗示的那样:如果您需要这个日期范围，“所有日期”表可能比 subD 子查询更方便；制作 SELECT DATE(X) UNION SELECT DATE(Y) UNION SELECT DATE(Z) UNION SELECT DATE(etc...) 子查询很快变得荒谬。