准备
DROP TABLE IF EXISTS `num`;
CREATE TABLE `num` (
`i` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `num` VALUES ('0');
INSERT INTO `num` VALUES ('1');
INSERT INTO `num` VALUES ('2');
INSERT INTO `num` VALUES ('3');
INSERT INTO `num` VALUES ('4');
INSERT INTO `num` VALUES ('5');
INSERT INTO `num` VALUES ('6');
INSERT INTO `num` VALUES ('7');
INSERT INTO `num` VALUES ('8');
INSERT INTO `num` VALUES ('9');
一天中每小时
select xc as date from (SELECT @xi:=@xi+1 as xc from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
(SELECT @xi:=-1) xc0) xx where xc < 24;
一段时间内每天
SELECT
adddate('2018-12-01', numlist.id) AS date
FROM
(
SELECT
n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000 + n10000.i * 10000 AS id
FROM
num n1
CROSS JOIN num AS n10
CROSS JOIN num AS n100
CROSS JOIN num AS n1000
CROSS JOIN num AS n10000
) AS numlist
WHERE
adddate(
'2018-12-01', numlist.id) <= '2018-12-22';
一段时间内每月
DATE_FORMAT(
date_add(
DATE_ADD(now(), INTERVAL - 11 MONTH),
INTERVAL numlist.id MONTH
),
'%Y-%m'
) AS date
FROM
(
SELECT
n1.i + n10.i * 10 AS id
FROM
num n1
CROSS JOIN num AS n10
) AS numlist
WHERE
date_add(
DATE_ADD(now(), INTERVAL - 11 MONTH),
INTERVAL numlist.id MONTH
) <= DATE_ADD(now(), INTERVAL 0 MONTH);