Я думаю, что самым простым общим решением этой проблемы было бы создание таблицы Ordinal
с наибольшим количеством требуемых строк (в вашем случае 31 * 3 = 93).
CREATE TABLE IF NOT EXISTS `Ordinal` (
`n` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`n`)
);
INSERT INTO `Ordinal` (`n`)
VALUES (NULL), (NULL), (NULL); #etc
Затем сделайте LEFT JOIN
из Ordinal
на свои данные. Вот простой случай, получаемый каждый день на прошлой неделе:
SELECT CURDATE() - INTERVAL `n` DAY AS `day`
FROM `Ordinal` WHERE `n` <= 7
ORDER BY `n` ASC
Две вещи, которые вам нужно изменить, это начальная точка и интервал. Я использовал синтаксис SET @var = 'value'
для ясности.
SET @end = CURDATE() - INTERVAL DAY(CURDATE()) DAY;
SET @begin = @end - INTERVAL 3 MONTH;
SET @period = DATEDIFF(@end, @begin);
SELECT @begin + INTERVAL (`n` + 1) DAY AS `date`
FROM `Ordinal` WHERE `n` < @period
ORDER BY `n` ASC;
Таким образом, окончательный код выглядел бы примерно так, если бы вы присоединились к числу сообщений в день за последние три месяца:
SELECT COUNT(`msg`.`id`) AS `message_count`, `ord`.`date` FROM (
SELECT ((CURDATE() - INTERVAL DAY(CURDATE()) DAY) - INTERVAL 3 MONTH) + INTERVAL (`n` + 1) DAY AS `date`
FROM `Ordinal`
WHERE `n` < (DATEDIFF((CURDATE() - INTERVAL DAY(CURDATE()) DAY), ((CURDATE() - INTERVAL DAY(CURDATE()) DAY) - INTERVAL 3 MONTH)))
ORDER BY `n` ASC
) AS `ord`
LEFT JOIN `Message` AS `msg`
ON `ord`.`date` = `msg`.`date`
GROUP BY `ord`.`date`
Советы и комментарии:
Ordinal
. Для сравнения, преобразование этой целочисленной последовательности в даты было простым. Ordinal
для всех ваших потребностей в непрерывной последовательности. Просто убедитесь, что он содержит больше строк, чем ваша самая длинная последовательность. Ordinal
для нескольких последовательностей, например, перечисляя каждый будний день (1-5) за последние семь (1-7 ) недель. Ordinal
, но она будет менее гибкой. Таким образом, вам нужна только одна таблица Ordinal
, независимо от того, сколько раз вы ее используете. Тем не менее, если скорость стоит того, попробуйте синтаксис INSERT INTO ... SELECT
.