以前，我正在执行以下操作以从报告表中获取每日计数。
SELECT COUNT(*) AS count_all, tracked_on
FROM `reports`
WHERE (domain_id = 939 AND tracked_on >= '2014-01-01' AND tracked_on <= '2014-12-31')
GROUP BY tracked_on
ORDER BY tracked_on ASC;
显然，这不会给我丢失日期的 0 计数。
然后我终于找到了一个 optimum solution 来生成给定日期范围之间的日期序列。
但我面临的下一个挑战是将它与我的报告表连接起来并按日期分组计数。
select count(*), all_dates.Date as the_date, domain_id
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) all_dates
inner JOIN reports r
on all_dates.Date >= '2014-01-01'
and all_dates.Date <= '2014-12-31'
where all_dates.Date between '2014-01-01' and '2014-12-31' AND domain_id = 939 GROUP BY the_date order by the_date ASC ;
我得到的结果是
count(*) the_date domain_id
46 2014-01-01 939
46 2014-01-02 939
46 2014-01-03 939
46 2014-01-04 939
46 2014-01-05 939
46 2014-01-06 939
46 2014-01-07 939
46 2014-01-08 939
46 2014-01-09 939
46 2014-01-10 939
46 2014-01-11 939
46 2014-01-12 939
46 2014-01-13 939
46 2014-01-14 939
...
而我希望用 0 填充缺失的日期
就像是
count(*) the_date domain_id
12 2014-01-01 939
23 2014-01-02 939
46 2014-01-03 939
0 2014-01-04 939
0 2014-01-05 939
99 2014-01-06 939
1 2014-01-07 939
5 2014-01-08 939
...
我的另一个尝试是:
select count(*), all_dates.Date as the_date, domain_id
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) all_dates
inner JOIN reports r
on all_dates.Date = r.tracked_on
where all_dates.Date between '2014-01-01' and '2014-12-31' AND domain_id = 939 GROUP BY the_date order by the_date ASC ;
结果:
count(*) the_date domain_id
38 2014-09-03 939
8 2014-09-04 939
上述查询的最少数据:http://sqlfiddle.com/#!2/dee3e/6
最佳答案
您需要一个 OUTER JOIN 在开始和结束之间的每一天到达，因为如果您使用 INNER JOIN 它会将输出限制为仅连接的日期(即仅报告表中的那些日期)。
此外，当您使用 OUTER JOIN 时，您必须注意 where clause 中的条件不会导致 implicit inner join ；例如 AND domain_id = 1 如果在 where 子句中使用将抑制不满足该条件的任何行，但当用作连接条件时，它仅限制报告表的行。
SELECT
COUNT(r.domain_id)
, all_dates.Date AS the_date
, domain_id
FROM (
SELECT DATE_ADD(curdate(), INTERVAL 2 MONTH) - INTERVAL (a.a + (10 * b.a) ) DAY as Date
FROM (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
) all_dates
LEFT OUTER JOIN reports r
ON all_dates.Date = r.tracked_on
AND domain_id = 1
WHERE all_dates.Date BETWEEN '2014-09-01' AND '2014-09-30'
GROUP BY
the_date
ORDER BY
the_date ASC;
我还更改了 all_dates 派生表，通过使用
DATE_ADD() 将起点推到 future ，并减小了它的大小。这两个都是选项，可以根据需要进行调整。Demo at SQLfiddle
要获得每一行的 domain_id(如您的问题所示)，您需要使用以下内容；请注意，您可以使用特定于 MySQL 的
IFNULL()，但我使用了更通用的 SQL 的 COALESCE()。然而，这里显示的@parameter 的使用无论如何都是特定于 MySQL 的。SET @domain := 1;
SELECT
COUNT(r.domain_id)
, all_dates.Date AS the_date
, coalesce(domain_id,@domain) AS domain_id
FROM (
SELECT DATE_ADD(curdate(), INTERVAL 2 month) - INTERVAL (a.a + (10 * b.a) ) DAY as Date
FROM (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
CROSS JOIN (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
) all_dates
LEFT JOIN reports r
ON all_dates.Date = r.tracked_on
AND domain_id = @domain
WHERE all_dates.Date BETWEEN '2014-09-01' AND '2014-09-30'
GROUP BY
the_date
ORDER BY
the_date ASC;
See this at SQLfiddle
关于MySQL 按日期和计数分组，包括缺少的日期，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25804531/