分享程序员开发的那些事...
更新时间：2022-04-05 06:20:56
MySQL没有递归功能，因此您只需要使用NUMBERS表技巧-
MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -
创建仅包含递增数字的表-使用auto_increment易于完成:
Create a table that only holds incrementing numbers - easy to do using an 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;
使用以下表格填充表格:
Populate the table using:
INSERT INTO `example`.`numbers`
( `id` )
VALUES
( NULL )
...根据需要提供任意数量的值.
...for as many values as you need.
使用 DATE_ADD 构建日期列表，并根据NUMBERS.id值增加日期.用您各自的开始和结束日期替换"2010-06-06"和"2010-06-14"(但使用相同的格式，YYYY-MM-DD)-
Use DATE_ADD to construct a list of dates, increasing the days based on the NUMBERS.id value. Replace "2010-06-06" and "2010-06-14" with your respective start and end dates (but use the same format, 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 JOIN onto your table of data based on the time portion:
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`
If you want to maintain the date format, use the DATE_FORMAT function:
DATE_FORMAT(`x`.`ts`, '%d.%m.%Y') AS `timestamp`