MySQL没有递归功能,所以你留下使用NUMBERS表技巧 –
>创建一个只保存递增数字的表格 – 使用auto_increment很容易做到:
DROP TABLE IF EXISTS `example`.`numbers`;
CREATE TABLE `example`.`numbers` (
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>填充表使用:
INSERT INTO `example`.`numbers`
( `id` )
VALUES
( NULL )
…为您的需要尽可能多的值。
>使用DATE_ADD构建日期列表,根据NUMBERS.id值增加日期。将“2010-06-06”和“2010-06-14”替换为您各自的开始和结束日期(但使用相同的格式,YYYY-MM-DD) –
SELECT `x`.*
FROM (SELECT DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY)
FROM `numbers` `n`
WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` -1 DAY) <= '2010-06-14' ) x
>根据时间部分,将LEFT加入您的数据表中:
SELECT `x`.`ts` AS `timestamp`,
COALESCE(`y`.`score`, 0) AS `cnt`
FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY), '%m/%d/%Y') AS `ts`
FROM `numbers` `n`
WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY) <= '2010-06-14') x
LEFT JOIN TABLE `y` ON STR_TO_DATE(`y`.`date`, '%d.%m.%Y') = `x`.`ts`
DATE_FORMAT(`x`.`ts`, '%d.%m.%Y') AS `timestamp`