有几种不同的方法可以获得连续日期的结果。我最喜欢的是使用虚拟表或AI PK中现有的一组连续ID来创建所需的完整集。像这样的东西 -
SELECT '2011-01-01' + INTERVAL (id -1) DAY
FROM dummy
WHERE id BETWEEN 1 AND 365这将返回2011年的整套天数,然后可以将LEFT JOIN加入您的电子邮件表以获取计数 -
SELECT `dates`.`date`, COUNT(emails.id)
FROM (
SELECT '2011-01-01' + INTERVAL (id - 1) DAY AS `date`, '2011-01-01 23:59:59' + INTERVAL (id - 1) DAY AS `end_of_day`
FROM dummy
WHERE id BETWEEN 1 AND 365
) `dates`
LEFT JOIN emails
ON `emails`.`datetime` BETWEEN `dates`.`date` AND `dates`.`end_of_day`
GROUP BY `dates`.`date`要填充您的dummy / seq表,您可以手动插入前十个值,然后使用INSERT ... SELECT添加其余值 -
CREATE TABLE dummy (id INTEGER NOT NULL PRIMARY KEY);
INSERT INTO dummy VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
SET @tmp := (SELECT MAX(id) FROM dummy) + 1;
INSERT INTO dummy
SELECT @tmp + id
FROM dummy;您需要在每次运行INSERT ... SELECT查询之前执行SET查询。